# 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!A1How 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

#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")OrYou 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