Dynamic Path to Sheet Name, Excel

[rossi45]

I have an excel work book with a sheet I use to cross reference external table names to their associated lookup tables 2 columns. The first column is a list of table names. The second column is a list of the tables associated lookup table. See image

Tabbles.jpg

The lookup tables are additional sheets in the same workbook. So I have 5 additional sheets called 'Table1_lookup', 'Table2_lookup', 'Table3_lookup', 'Table4_lookup', 'Table5_lookup'

I want to dynamically set the Lookup table names to it's associated sheet, so that if someone changes the sheet name, the cell in the 'LOOKUP_TABLE' column in the cross reference table above, will automatically update as well. is there a way to accomplish this with a simple formula? I've seem ways to hard code to the sheet name, but when the sheet name changes, the link is broken.

Thanks, Mike

[tony h]

you could use a vba function to use the sheets codename to return the currentname. But it does mean that you would have to enable vba and it wouldn't refresh automatically. You would need some event eg opening the workbook, or clicking a button etc to cause the update.

If you need help let me know.