change a cell formula

  • Thread starter Thread starter JimP
  • Start date Start date
J

JimP

To All,

I need a cell on a sheet named "YEAR" to alternate between 2 possible
array formulas (stored as TEXT on a sheet named "WorkSheet").

Could someone advise how to setup these 2 formulas properly as strings
AND then how to copy the 2 formula's to cell A1 on the sheet "YEAR"
???

=IF(B$4="","",INDEX(WorkSheet!Sked_Cycle_Range,HLOOKUP(LEFT($A7,1),WorkSheet!Which_Row,2,FALSE),WorkSheet!row_range,2,FALSE),(MOD(B$4-WorkSheet!Seed_Date,WorkSheet!SEQ)+1)))

and the 2nd:

=IF(B$4="","",INDEX(WorkSheet!Sked_Cycle_Range,HLOOKUP(HLOOKUP(LEFT($A7,1),
WorkSheet!Which_Row,2,FALSE)+MOD(4*(TRUNC((B$4-WorkSheet!Seed_Date)/WorkSheet!SEQ)),7),WorkSheet!row_range,2,FALSE),(MOD(B$4-WorkSheet!Seed_Date,WorkSheet!SEQ)+1)))

Any help wold be appreciated ...

Jim Pellechi
 
sf1 =
"=IF(B$4="""","""",INDEX(WorkSheet!Sked_Cycle_Range,HLOOKUP(LEFT($A7,1),Work
Sheet!Which_Row,2,FALSE),WorkSheet!row_range,2,FALSE),(MOD(B$4-WorkSheet!See
d_Date,WorkSheet!SEQ)+1)))"
sf2 =
"=IF(B$4="""","""",INDEX(WorkSheet!Sked_Cycle_Range,HLOOKUP(HLOOKUP(LEFT($A7
,1),

WorkSheet!Which_Row,2,FALSE)+MOD(4*(TRUNC((B$4-WorkSheet!Seed_Date)/WorkShee
t!SEQ)),7),WorkSheet!row_range,2,FALSE),(MOD(B$4-WorkSheet!Seed_Date,WorkShe
et!SEQ)+1)))"

or

With woksheets("worksheet")
sf1 = Trim(Range("A1").Value)
sf2 = Trim(Range("A2").Value)

with worksheets("Year")
if flag = 1 then
.Range("B9").FormulaArray = sf1
else
.Range('B9").FormulaArray = sf2
End If
End With
 
Tom (or any one else that understands this problem),

HELP ... a problem has crept in and now I'm doubting I understand the
basics.

As I improve my spreadsheet I rename it - a simple form of
Configuration Management.

The problem is that my formulas stopped working ... after debugging I
can see that for some unknown reason the name of the FILE that the
formula existed in PRIOR to my renaming the file, has become part of
the worksheet name I'm using within the formula????? Since I tend to
delete former versions of the spreadsheet - as soon as the file is
deleted, the next attempt at running the latest version of the
spreadsheet fails ...

I'm not even sure I'm explaining this properly.

Since I haven't a clue how this happened, I realize I don't understand
the significance and the 'hidden' relationship of a worksheets
FILENAME, WORKSHEET NAME, RANGE_NAME ... etc.

Could you instruct me on how to isolate the variable names I'm using
within a formula to ONLY the worksheet level within WHATEVER workbook
the formula resides in ...

Jim Pellechi
----------------------------------------------------------


Message 2 in thread
From: Tom Ogilvy ([email protected])
Subject: Re: change a cell formula


View this article only
Newsgroups: microsoft.public.excel.programming
Date: 2003-11-04 19:11:34 PST


sf1 =
"=IF(B$4="""","""",INDEX(WorkSheet!Sked_Cycle_Range,HLOOKUP(LEFT($A7,1),Work
Sheet!Which_Row,2,FALSE),WorkSheet!row_range,2,FALSE),(MOD(B$4-WorkSheet!See
d_Date,WorkSheet!SEQ)+1)))"
sf2 =
"=IF(B$4="""","""",INDEX(WorkSheet!Sked_Cycle_Range,HLOOKUP(HLOOKUP(LEFT($A7
,1),

WorkSheet!Which_Row,2,FALSE)+MOD(4*(TRUNC((B$4-WorkSheet!Seed_Date)/WorkShee
t!SEQ)),7),WorkSheet!row_range,2,FALSE),(MOD(B$4-WorkSheet!Seed_Date,WorkShe
et!SEQ)+1)))"

or

With woksheets("worksheet")
sf1 = Trim(Range("A1").Value)
sf2 = Trim(Range("A2").Value)

with worksheets("Year")
if flag = 1 then
.Range("B9").FormulaArray = sf1
else
.Range('B9").FormulaArray = sf2
End If
End With
 
UPDATE:
Problem solved, bought the book Excel 2002 Formulas, by John
Walkenbach.
Chapter 3 is devoted entirely to WORKING WITH NAMES ... and within
those (36) pages I was educated to all I ever wanted to know about
NAMES and more ... From there it was easy to see what happen and how
to correct it.

JimP
 
Back
Top