Change Tab name where other sheets are linked to it by formula

pedro-egoli

Well-known Member
Joined
Apr 25, 2004
Messages
1,217
Office Version
  1. 2016
Platform
  1. Windows
To complete a workbook I sought assistance on yesterday I now want to rename a Tab from "a very long name" to "short name".
Trouble is that other sheets in the workbook relate back to this Tab via "='very long name!a1' "
Tried to replace "a very long name" with "short name" but ran into problems with the resultant window that opened and computer froze.

Is there a way to change the tab name and any formulae in other sheets reliant on data in that tab, without manually redoing each formula.

Pedro
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
To complete a workbook I sought assistance on yesterday I now want to rename a Tab from "a very long name" to "short name".
Trouble is that other sheets in the workbook relate back to this Tab via "='very long name!a1' "
Tried to replace "a very long name" with "short name" but ran into problems with the resultant window that opened and computer froze.

Is there a way to change the tab name and any formulae in other sheets reliant on data in that tab, without manually redoing each formula.

Pedro
I'm not exactly sure what the problem is that you are describing. If you re-name a worksheet, all formulas in the workbook that refer to that sheet should update to the new name automatically.

Perhaps you need to more fully describe the "resulatant window that opened".
 
Upvote 0
I've just tried renaming a tab manually and using VBA code and in both cases the formulas on other sheets correctly referenced the revised sheet name.

Can you describe more precisely the issue that you have - the only way that I can generate a #REF error is by deleting the referenced sheet.
 
Upvote 0
pedro-egoli,

You should not have a problem.

Try doing the following in a new workbook.


Sample sheets:


Excel Workbook
A
1111
2222
3333
Sheet2



Excel Workbook
A
1111
2222
3333
a very long name




Then change the sheetname from "a very long name" to "short name":



Excel Workbook
A
1111
2222
3333
short name



And....


Excel Workbook
A
1111
2222
3333
Sheet2



Have a great day,
Stan
 
Upvote 0
Thanks for the very quick replies.

I was hesitant to change the Tab name , because I thought it would throw up an error in all the cells that relied on "the very long name".

Just renamed it and as Peter and Stan said it changed the formula accordingly.

Vog's vba came up with the same result too by the sounds of it.

Just shows a simple answer was made complicated by me because of an assumption.

Thanks again to Peter ,Stan and Vos for your help.

Pedro
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,236
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top