VLookup multiple spreadsheets

[Riley]

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

 

[Bernie Deitrick]

Riley,

With the sheet name in A1, and the lookup value in A2, something like this:

=VLOOKUP($A2,INDIRECT("'" & $A$1 & "'!A"),4,FALSE)

should work, and allow you to copy the formula down to match multiple keys.

HTH,
Bernie
MS Excel MVP

 

[Bernie Deitrick]

Riley,

With the sheet name in A1, and the lookup value in A2, something like this:

=VLOOKUP($A2,INDIRECT("'" & $A$1 & "'!A"),4,FALSE)

should work, and allow you to copy the formula down to match multiple keys.

HTH,
Bernie
MS Excel MVP