Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using VLOOKUP Across Multiple Spreadsheets | Excel Discussion (Misc queries) | |||
Vlookup won't work on these two particular spreadsheets | Excel Worksheet Functions | |||
comparing two spreadsheets using vlookup and compare | Excel Worksheet Functions | |||
using vlookup - how do I match 2 spreadsheets w/o same exact numb. | Excel Worksheet Functions | |||
How to update multiple links in multiple spreadsheets followin mo. | Excel Worksheet Functions |