I have seen several postings on having the ability to set up vlookup
with dynamic files, but am not able to get them to work effectively.

I would like to allow the user to enter a value from a drop down box
PHYSICIAN 1, for example - and have the VLOOKUP function retriev
information from a file with the same name. I currently have:
VLOOKUP(A2,[HARDCODED FILE NAME]sheet1!'range,3,FALSE)

This formula will pull from the correct file, but I am running int
memory issues and need to break the supporting file into smalle
segments.

I have created a file for one physician (F:\dssf\PHYSICIAN 1.XLS) tha
I would like to reference by concatenating the result of the user inpu
with the fixed path. Easy enough, but I can't get the VLOOKUP t
recognize this path as valid.

I see that in order to use INDIRECT, the supporting file needs to b
open. A formula cited in another threa
(VLOOKUP(B1,INDIRECT("["&A1&"]"Sheet1!$a$2:$b$7),2) does not seem t
work, but is what I'm trying to do.

Any help that anyone can offer would be greatly appreciated.

Thank you

--
Message posted from http://www.ExcelForum.com