Vlookup, External File Name to a Referenced Cell

[gjohn282]

I haven't been able to figure this one out in any of the previous threads so hopefully there's someone who can help me...

I'm working with multiple workbooks, about 50 of them, which are all the same layout and store employee data. Only the data inside is each workbook varies.

I would like to create a "Summary" workbook that pulls data from all 50 workbooks. However, I would like to use a combination of the vlookup formula as well as the different .xls file names. I already built a macro that pulls the .xls file names for me easily.

If I build a vlookup formula that pulls data from each workbook, what I find is that the only referenced file names varies (.xls). The rest of the formula is identical to the next. So to save time, is there a way to make the file name a referenced cell? I haven't been able to figure this one out. I tried using the INDIRECT formula, but that seems to only work if I want to reference the table array name (i.e. named range) and not necessarily the varying file names.

Example:

My File Names, My External Workbooks: JohnDoe.xls, JaneDoe.xls, JimDoe.xls, etc

Possible Formula in my New "Summary" Workbook:
=vlookup(AgentName,'C:\Documents and Settings\office\Desktop\JohnDoe.xls'!EmployeeData,2,False)

Since I already have a list of the file names...and I already have the vlookup formula working... how can I incorporate the two so that the file name points to a reference cell within my summary workbook? Thanks!

[gjohn282]

Is there anyone that can help?

[ron2k_1]

You can use INDIRECT to do that as well, the only downside is that it will not update unless the source workbook is open.

You can use PULL user-defined function.

Harlan does a pretty good job explaining how it is used here.