How to change lookup references
I have a master spreadsheet that calculates formulas from data in another,
multitab spreadsheet. I want to create one master formula that changes the links from one tab to another, which the calculations can reference. It can be done with a search-and-replace of the tab name, but I'd rather use a formula that all the formulas in the master spreadsheet can link into. I thought of using a concactenate formula to build the spreadsheet and tab name, but the calculation formulas don't recognize it. What am I missing? |
How to change lookup references
Guy,
You need to build the reference string within the INDIRECT function, along the lines of =VLOOKUP(A1,INDIRECT("'" & B2 & "'!A2:C10"),2, FALSE) Where B2 has the tab name. HTH, Bernie MS Excel MVP "Guy Yeakley" <Guy wrote in message ... I have a master spreadsheet that calculates formulas from data in another, multitab spreadsheet. I want to create one master formula that changes the links from one tab to another, which the calculations can reference. It can be done with a search-and-replace of the tab name, but I'd rather use a formula that all the formulas in the master spreadsheet can link into. I thought of using a concactenate formula to build the spreadsheet and tab name, but the calculation formulas don't recognize it. What am I missing? |
All times are GMT +1. The time now is 12:23 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com