How do I remove the sheet name from a named formula?

G

Gadget

I would like to use the same name to refer to the same set of cells on
different worksheets:
SheetTitle=$A$1
but when I try this it reverts to
SheetTitle=Sheet1!$A$1

Any ideas how to do it?

I could use =IF(,,,) if I knew how to look up the currently active sheet;
=IF(SheetName="Sheet1",TRUE,FALSE)
Any ideas how to reference the name of the sheet?
 
T

tjtjjtjt

Well, you could do something like this:
SheetTitle=!$A$1
The exclamation point means that the Name will refer to A1 on the active
worksheet.
Excel uses the SheetName to tell things apart.
If I try to give to different A1's the same name ... how do I know which is
which? Excel answers this question by adding the SheetName.

tj
 
D

Dave Peterson

I've read a few posts that say that this can cause trouble. Charles Williams
says "There are serious Excel bugs that may give incorrect calculation results"
when using this.

His alternate suggestion is to use this in the Refers to box:
=INDIRECT("$A$1")
 
Top