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

[pedro-egoli]

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

 

[Peter_SSs]

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".

 

[VoG]

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.

 

[stanleydgromjr]

pedro-egoli,

You should not have a problem.

Try doing the following in a new workbook.


Sample sheets:

Excel Workbook
	A
1	111
2	222
3	333
Sheet2

Excel Workbook
	A
1	111
2	222
3	333
a very long name

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

Excel Workbook
	A
1	111
2	222
3	333
short name

And....

Excel Workbook
	A
1	111
2	222
3	333
Sheet2

Have a great day,
Stan

 

[pedro-egoli]

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