I have 400 excel files with different names and only one worksheet, all the
filenames are of the form "yyyymmdd-closing_prices.xls", where yyymmdd
represent a date, the worksheets have the name "yyyymmdd-closing_prices"
corresponding to the name.

I also have a master Excel file in which I have a vlookup function looking
at these sheets "vlookup(B$1,'XXXXXXXX'!$1:$65000,3,0)" where XXXXXXX refers
to each of the 400files. as you can imagine, the array has to change with
each file name for the 400 entries, I have developed a lookup table in the
master file that automatically gives the file name and array (in text format)
next to any date I enter, but I cannot use it at the moment. How can I get
Vlookup() to use the lookup table in my master file instead of retyping the
filename and range manually.

Frustrated!!!