VLookup multiple spreadsheets
I have a database with about 50 spreadsheets, each containing data on
different deals. I'm looking for the best way to pull data to a master summary tab, which has a list of all the different tabs. I don't want to have to set up the vlook-up for all 50 spreadsheets if possible, but rather somehow use a code or formula to search all of the spreadsheets for a given name of a tab and then use a vlookup (of the first column within that spreadsheet) to pull data, since all the spreadsheets are set up the same. Also, I'm not too familiar with VBA, but is there a simple code to loop this process once I figure out how to have my formula search for the corresponding spreadsheet I'm looking for? The other thing is the spreadsheets all have different names (deal name). Any help would be much appreciated |
VLookup multiple spreadsheets
Riley,
With the sheet name in A1, and the lookup value in A2, something like this: =VLOOKUP($A2,INDIRECT("'" & $A$1 & "'!A:D"),4,FALSE) should work, and allow you to copy the formula down to match multiple keys. HTH, Bernie MS Excel MVP "Riley" wrote in message ... I have a database with about 50 spreadsheets, each containing data on different deals. I'm looking for the best way to pull data to a master summary tab, which has a list of all the different tabs. I don't want to have to set up the vlook-up for all 50 spreadsheets if possible, but rather somehow use a code or formula to search all of the spreadsheets for a given name of a tab and then use a vlookup (of the first column within that spreadsheet) to pull data, since all the spreadsheets are set up the same. Also, I'm not too familiar with VBA, but is there a simple code to loop this process once I figure out how to have my formula search for the corresponding spreadsheet I'm looking for? The other thing is the spreadsheets all have different names (deal name). Any help would be much appreciated |
All times are GMT +1. The time now is 11:45 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com