Linking Tab Names to an Index

L

Lee

I want to link the sheet names to a index schedule that I have so that if
the sheet names change these will be automatically be changed in the index
schedule. For example in the index sheet I might have:

Schedule Reference: Schedule Contents:

Schedule A Summary in tab
Schedule B Analysis

If I rename Schedule B to C I want it to change to automatically.

Any help much appreciated.

Cheers

Lee
 
F

Frank Kabel

Hi
Excel changes sheet name references automatically if you
use them in formulas
 
L

Lee

Frank

Sorry but I obviously wasn't being clear enough.

In my index Schedule I have "Schedule A" etc just typed in whereas the
schedule contents are linked to the title of the sheet. If I change the
sheet name (for example by introducing a new sheet thereby moving "Schedule
B" down to "Schedule C") then I want that to be automatically updated in the
index schedule. This will save me from manually changing each time.

Lee
 
E

Earl Kiosterud

Lee,

Can you just use "Summary" or "Detail" in your workbook, and skip the
Schedule part?
 
T

Tushar Mehta

If I understand you correctly, you want the name of a worksheet to
remain synchronized with the contents of a cell.

If so, use the following:

In the Index worksheet, in the cell where you want the name of a
particular worksheet ('Schedule C' in my example), enter the formula

=MID(CELL("filename",'Schedule C'!$A$1),FIND("]",CELL
("filename",'Schedule C'!$A$1))+1,255)

Now, if the name of the worksheet 'Schedule C' changes to 'Schedule B'
XL will automatically update the cell containing the formula.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions
 
Top