change tab names in excel formula

March 9, 2009 at 18:59:31
Specs: Excel
I have an excel file with one cover sheet and 500 tabs. Each of the 500 tabs represents one company using this company's name as tab name. I have exactly the same format in all 500 tabs. e.g. this company's 2008 revenue in cell A1 of the tab.
In the cover sheet tab, I have 500 company names in column A. I want to return 2008 revenue to column B matching the company name in column A. The formula to read the revenue from each tab is this:
tabname!A1
How can I set up the formula so the tabname part of the formula can be automatically updated using the column A?
Many many thanks!

See More: change tab names in excel formula

Report •


#1
March 10, 2009 at 06:56:50
This formula will return the Tab Name of the sheet it is used in.

=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,LEN(CELL("filename",A1))-FIND("]",CELL("filename",A1)))

Modifying the A1 reference to include the Excel based sheet name will return the Tab Name for the new reference:

=MID(CELL("filename",Sheet2!A1),FIND("]",CELL("filename",Sheet2!A1))+1,LEN(CELL("filename",Sheet2!A1))-FIND("]",CELL("filename",Sheet2!A1)))

Combining this with the INDEX function you can return the value in a given cell, e.g. Sheet2!B25, even if you change the name of the Tab. (This could go in Column B)

=INDIRECT(MID(CELL("filename",Sheet2!A1),FIND("]",CELL("filename",Sheet2!A1))+1,LEN(CELL("filename",Sheet2!A1))-FIND("]",CELL("filename",Sheet2!A1)))&"!B25")

Or

You could use the first formula in Column A to return the Tab Name, and then use this in Column B:

=INDIRECT(A1&"!B25")


Report •

#2
March 13, 2009 at 10:58:23
Great. It worked. Thanks lot.

Report •

Related Solutions


Ask Question