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

(JimP) wrote in message . com...
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 )
Subject: 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_Rang e,HLOOKUP(LEFT($A7,1),Work
Sheet!Which_Row,2,FALSE),WorkSheet!row_range,2,FAL SE),(MOD(B$4-WorkSheet!See
d_Date,WorkSheet!SEQ)+1)))"
sf2 =
"=IF(B$4="""","""",INDEX(WorkSheet!Sked_Cycle_Rang e,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

--
Regards,
Tom Ogilvy


James Pellechi wrote in message ...
WORKS like a charm Tom, Thanks again ...

Jim Pellechi

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!