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:
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 •

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


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


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)



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


Report •

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

Report •

Related Solutions

Ask Question