Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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!!! |
#2
|
|||
|
|||
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 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!!! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I use a cell as a referance to a file in a lookup statemen. | Excel Worksheet Functions | |||
Read Text File into Excel Using VBA | Excel Discussion (Misc queries) | |||
File asks to update when no links were created | Links and Linking in Excel | |||
format question when open csv file | Excel Discussion (Misc queries) | |||
double lookup, nest, or macro? | Excel Worksheet Functions |