Dynamic Path to Sheet Name, Excel

Question

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

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

with VBA you could use Sheet's CodeName property and than make a reference to the sheet name. That's just idea which could be useful while I still see some problems to provide anything more. — Kazimierz Jawor

pnuts pnuts · Accepted Answer · 2014-01-04T21:11:57

Use a series of formulae such as:

=RIGHT(CELL("filename",Table1_Lookup!A$1),LEN(CELL("filename",Table1_Lookup!A$1‌))-FIND("]",CELL("filename",Table1_Lookup!A$1)))

Dynamic Path to Sheet Name, Excel

1 Answers