VLookup multiple spreadsheets

R

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
 
B

Bernie Deitrick

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top