defining a cell in multiple worksheets

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Call me stupid, but I've read the specific responses to this question on 4/12, 4/27 and 4/28 but for the life of me can't get it to do what I want. Which is: say I've named cell A1 in Worksheet1 "FV" which is used in a formula that is consistent in Worksheets2, 3, etc.-that is it refers to the LOCAL cell A1 for each of those worksheets, and can be a different value for each. If I copy Worksheet1 and rename it, then yes, it works fine - ie. FV is peculiar to cell A1 in each Worksheet. However, if I create a new worksheet and try and name cell A1 "FV", it either defaults to Worksheet1!A1 or I end up overwriting FV to be NewWorksheet!A1 which becomes the new global value for FV and perpetuates the problem. Hope this is clear...all I want to do is find out how to go to one worksheet after another and define A1 as FV for that worksheet only. Thanks.
 
Hi Spearfisher,

If I'm reading this correctly you need to use a dynamic sheet reference.

For instance, where you are entering the formula in the 'refers to' box,
use:


=!$A$1

Now FV will refer to $A$1 on the sheet in which it is used. You don't need
to re-define it for any sheet, copied or new, because the name FV will now
always refer to the sheet of the formula that it is used in, regardless of
the sheet that was selected when you made the definition.

Steve D.



Spearfisher said:
Call me stupid, but I've read the specific responses to this question on
Click to expand...
4/12, 4/27 and 4/28 but for the life of me can't get it to do what I want.
Which is: say I've named cell A1 in Worksheet1 "FV" which is used in a
formula that is consistent in Worksheets2, 3, etc.-that is it refers to the
LOCAL cell A1 for each of those worksheets, and can be a different value for
each. If I copy Worksheet1 and rename it, then yes, it works fine - ie. FV
is peculiar to cell A1 in each Worksheet. However, if I create a new
worksheet and try and name cell A1 "FV", it either defaults to Worksheet1!A1
or I end up overwriting FV to be NewWorksheet!A1 which becomes the new
global value for FV and perpetuates the problem. Hope this is clear...all I
want to do is find out how to go to one worksheet after another and define
A1 as FV for that worksheet only. Thanks.
 
Spearfisher, to create a local name (one good for that worksheet only),
select Cell A1, click in the Name box, type SheetName!FV, and then press
Enter. Substitute the actual worksheet name for "SheetName."

If you copy the worksheet, Excel will copy the name along with it, where
that name will be local to that worksheet.

--
DDM
"DDM's Microsoft Office Tips and Tricks"
Visit us at www.ddmcomputing.com


Spearfisher said:
Call me stupid, but I've read the specific responses to this question on
Click to expand...
4/12, 4/27 and 4/28 but for the life of me can't get it to do what I want.
Which is: say I've named cell A1 in Worksheet1 "FV" which is used in a
formula that is consistent in Worksheets2, 3, etc.-that is it refers to the
LOCAL cell A1 for each of those worksheets, and can be a different value for
each. If I copy Worksheet1 and rename it, then yes, it works fine - ie. FV
is peculiar to cell A1 in each Worksheet. However, if I create a new
worksheet and try and name cell A1 "FV", it either defaults to Worksheet1!A1
or I end up overwriting FV to be NewWorksheet!A1 which becomes the new
global value for FV and perpetuates the problem. Hope this is clear...all I
want to do is find out how to go to one worksheet after another and define
A1 as FV for that worksheet only. Thanks.
 
Hi Stephen
I suggested the same solution like you did but Charles Williams
correctly pointed out that this kind of defined names could cause some
serious bugs. He recommended using a formula like
=INDIRECT("$A$1")
as 'refer to' value in the name definition. Same result but more robust
 
Back
Top