specifying file name in v lookup

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!!!

I am not certain I understand what you have in this table in your master file,
but have you looked at whether the INDIRECT function will help? If I put a
workbook name in A1, this formula returns the value from A1 in that workbook.

=INDIRECT("["&A1&"]Sheet1!$A$1")

I wonder if your layout is optimal for this sort of thing. Have you considered
a single worksheet with your tickers in row 1 and the dates in column 1? Then
you could use a formula like

=INDEX(Sheet1$A$1:$Z$65000,MATCH(TheTicker,Sheet1!$1:$1,0),MATCH(TheDate,Sheet1!$A:$A,0)

Or, if you have more than 256 tickers, you could put the data into a table in
Access, with the table fields being Ticker, Date, and Price, then use Access
Queries to get the data, or a Pivot Table in Excel.


On Wed, 9 Feb 2005 02:05:03 -0800, Word4Dummies
<Word4Dummies@discussions.microsoft.com> wrote:

>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!!!