Refer to sheets without using tab name??

[Robert Crandal]

Cell formulas that refer to other sheets usually look like
this:

=Sheet2!A1 ' Get cell A1 from Sheet2

However, what if someone changed the name of the
Sheet2 tab to something like "ThisIsMyTab"??? Does that
mean I would have to manually change all possible formulas
to the following:

=ThisIsMyTab!A1

?????

I'm mostly curious if there's a way to use formulas that
do not strictly rely on the names of always changing
tab sheet names.

thank you

 

[Charabeuh]

Hello,

The best way is to test it by yourself.

In Excel 2007, formulas that refer to another sheet (in the same workbook)
will be automatically changed by excel if you change the name of any sheet
of the workbook.

 

[Paul E]

I have not tested this, but it would seem that you could use a range name w/
Global scope. Even if this was just used to select the sheet and make it
active, it would be a good anchor. Once active, you could get the sheet name
and cobble together the formula using a variable w/ the sheet name it it.

 

[Barb Reinhardt]

I'[m thinking you're looking for something like this

http://www.ozgrid.com/VBA/excel-vba-sheet-names.htm

 

[Dave Peterson]

#1. No. Those kinds of formulas wouldn't need to change.
#2. You may find a way, but it'll probably slow excel down--and it'll be easy
to make mistakes.
#3. Why do you have sheets changing names so often?