How do I change the Sheet name without updating formulas?

[BodiKlamph]

Hi

As the subject says: All I want to do is change the sheet name from
"Formulas" to "Formulas_Old" without all of my formulas updating to
the new sheetname.

Possible?

 

[BodiKlamph]

One way with Excel 2003 is to make a copy of the sheet, rename it as
desired,  and then delete the original one. Naturally links to the sheet
from others will be broken because they refer to the now-deleted sheet.

I'm not sure what you intend for links within the Formulas sheet itself
that say "=Formalas!..." for some reason. These will update when copying.

Thx...That won't work. I'll be more detailed.

I have a sheet called "Order" that has a bunch of formulas that do a
VLOOKUP to a sheet called "Formulas". A bunch of our business rules
changed so we created a new "Formulas" sheet.

We now have the "Order" sheet referring to "Formulas!A1:C10" and 2
sheets called "Formulas" and "Formulas_Old".

Depending on which sales option they choose, I want the "Order" sheet
to refer to either "Formulas" or "Formulas_Old".

I don't want a massive IF() statement depending on the sales option.
I created code that changed all of the formulas to point to either
"Formula" or "Formulas_Old", but this could be buggy if it crashes
hafl way through, or whatever.

I want to simply swap the names of the sheets, but as you know this
changes the formulas automatically.

Ideas?

 

[Duke Carey]

One way - use the Indirect() function to reference your vlookup() table
range. Indirect converts a text string that looks like a range address to an
actual range address.

So, with a range named Table on the Formulas sheet and a range named Table
on the Formulas_Old sheet, this formula will sum the Table range on whichever
sheet is referenced in cell E3

=SUM(INDIRECT(E3&"!table"))

 

[BodiKlamph]

One way - use the Indirect() function to reference your vlookup() table
range. Indirect converts a text string that looks like a range address toan
actual range address.

So, with a range named Table on the Formulas sheet and a range named Table
on the Formulas_Old sheet, this formula will sum the Table range on whichever
sheet is referenced in cell E3

=SUM(INDIRECT(E3&"!table"))












- Show quoted text -

beautiful, thanks!