nanaming tabs to fit formulas

[TheMiz]

Hi there, I have a worksheet with vlookups referencing other tabs in the workbook. My plan is to replace a few of those tabs, so I have created new worksheets and tried to just change the name of the new tab to match the old one (so the vlookups will still work).


However I get an error stating "Cannot rename a sheet to the same name as another sheet, referenced object library or a workbook referenced by Visual Basic". I have changed the name of the original tab but the vlookups just change to the new tab name.


Is there any way to work around this? Thanks!


xpost-http://www.excelbanter.com/showthread.php?p=1609217#post1609217

 

[wigi]

Hello

I am not following you entirely, why don't you just the rename the sheet(s) right away.
Excel will adapt the formulas for you, automatically.
Unless you use functions like INDIRECT where the sheet name is hardcoded as text, rather than a cell reference to a different sheet.

 

[TheMiz]

Hello

I am not following you entirely, why don't you just the rename the sheet(s) right away.
Excel will adapt the formulas for you, automatically.
Unless you use functions like INDIRECT where the sheet name is hardcoded as text, rather than a cell reference to a different sheet.

Thanks for responding wigi. I'll explain some more.

Right now I have a sheet with vlookups referencing other sheets (tab A, tab B, tab C). I have to replace tabs A-C and created different tabs. When I change the name of Tab A (so that I can rename the new tabs to Tab A), the vlookups change to whatever I rename them to. So how do I get the vlookups to reference the new tabs I created?

Hope this helps. Thanks again!

 

[wigi]

Hello

Either you keep on working with tabs A, B, C but change their contents. The VLOOKUPs can still refer the same sheet names.
Either you add several tabs and do 1 or more Find/Replace actions to replace sheet name "x" into sheet name "y".