Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
find files from a dropdown list
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? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
find files from a dropdown list
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 --- " wrote: 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? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
find files from a dropdown list
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 he 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 " wrote: 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
I can not find the list command from the data dropdown menu? | Excel Worksheet Functions | |||
result of selecting from the dropdown list should be a dropdown list | Excel Worksheet Functions | |||
result of selecting from the dropdown list should be a dropdown list | Excel Worksheet Functions | |||
Using dropdown list to find file | Excel Discussion (Misc queries) | |||
dropdown list...no help in the help files | New Users to Excel |