find files from a dropdown list

[pmyers123]

I have a group of files named sequentially by date. I want to use a dropdown
list to select which file to pull information from. How can I insert the
filename from the dropdown list into a vlookup formula to get the information
from the correct file?

 

[Max]

Think INDIRECT can be used but the source file needs to be simultaneously
open for it to work

In say Book2.xls,
Assume the source filename is DV selected in A5, eg: Book1.xls

Provided Book1.xls is simultaneously open
then something like this will work:
=VLOOKUP(C5,INDIRECT("'["&A5&"]Sheet1'!A:B"),2,0)
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:23,000 Files:370 Subscribers:66
xdemechanik

 

[Shane Devenshire]

Hi,

Suppose the drop down list is in A1 then the general VLOOKUP formula could be

=VLOOKUP(C5,INDIRECT(A1),n,FALSE)

However, most likely it will look more like this:

=VLOOKUP(C1,INDIRECT("'["&A1&".xls]Manual Grouping'!$A$1:$C$21"),2,FALSE)

because the item on the drop down list may only be the file name and not
include the sheet and the range and the xls part, etc.

In this case of course the sheet name and range must be the same in all files.

Now for the real problem here: INDIRECT only works with Open files, if the
files are close then it will return an error.

Lotus 1-2-3 allowed references to close files with indirect back in 1982 and
today other spreadsheet also do, even the free Open Office, but not Excel.


If this helps, please click the Yes button

Cheers,
Shane Devenshire