CODEWITHSUNDEEP
excelexcel-formula
Mike
Mike

Reputation: 4435

Dynamic Path to Sheet Name, Excel

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

Upvotes: 0

Views: 762

Answers (1)

pnuts
pnuts

Reputation: 59495

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)))

Upvotes: 1

Related Questions