if the file is to large you sould prob try access

i have effectively used xl lookup and reffrence
funcintions with 1000's of recoreds but when you get into
the 10000+ range you can run into problems

if your file is not as large as im talking, then thier is
another problem and i would have to have more info to help

ps

feel free to emial me more info if you want

or post, but i leave work and do not check this site on
weekends, cause thier are lots of peops smarter then me
here


-----Original Message-----
I have seen several postings on having the ability to
set up vlookups
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
retrieve
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 into
memory issues and need to break the supporting file into
smaller
segments.

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

I see that in order to use INDIRECT, the supporting file
needs to be
open. A formula cited in another thread
(VLOOKUP(B1,INDIRECT("["&A1&"]"Sheet1!$a$2:$b$7),2) does
not seem to
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/

.