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

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/

.

As it says in help for the indirect function, it doesn't work with closed
files. You haven't said explicitly that the file is closed, but you have
alluded to that.

also, if it is open, then you need to use single quotes since there is a
space in the name

VLOOKUP(B1,INDIRECT("'["&A1&"]"Sheet1'!$a$2:$b$7"),2)

so the concatenation resolves to

'[PHYSICIAN 1.xls]Sheet1'!A2:B2

No need to use $ signs in your string - it is a string, not a cell
reference, until it is evaluated by INDIRECT.

--
Regards,
Tom Ogilvy


"Thomas23 " wrote in 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/

Thank you both for your responses. Unfortunately, I am not able to us
MSAccess. It would have been my preference to set this up in anothe
way - SAS or SQL probably... This system was set up prior to m
arrival.

I do intend on opening the file - VB macro process. I would have like
to handle this piece through VB as well, but it's a bit beyond what I'
comfortable doing. I saw the formula in the postings on this site an
hoped that would work.

Tom - thanks for the hint. It could be the way the quotes are set u
that is not allowing the function to work. I'll give that a shot an
let you know how it goes.

Thank you!

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

I thought that may do the trick, but I'm still finding errors...

I'm going to simplify this just for my own sanity. (Both of th
following files are open.)

File 1: norlanco.xls - has data that I need to pull in

data is:
norlanco 123 321 312

File 2: Master_sheet.xls - where select data will be shown.

A1 is file reference - no concatenation for now, just typed in.
'[norlanco.xls]Sheet1'!A2:d3
{could single tick being taken as a justification be an issue?}
{Do I need double single ticks at beginning to counter?}

E1 is the lookup criteria: norlanco in this case - no single quotes

I want to look for norlanco within the file norlanco.xls and take th
2nd number in the VLOOKUP - 123.

This formula works:
=VLOOKUP(E1,[norlanco.xls]Sheet1!$A$1:$E$2,2,FALSE)

The application of the INDIRECT is what's throwing me off...



Everything that you have said and what is in the articles makes sense
I hope that it is just a small format issue that I am not correctl
handling... Single quotes in the file reference maybe?


Again - any help is greatly appreciated...

Thank you!

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

if you put
'888

in a cell, you only see 888 and the 888 is treated as text, rather than a
number. So the first single quote is treated as a formatting character.
This is your problem.

''[norlanco.xls]Sheet1'!A2:d3

with two single quotes worked fine for me.

--
Regards,
Tom Ogilvy



"Thomas23 " wrote in message
...
I thought that may do the trick, but I'm still finding errors...

I'm going to simplify this just for my own sanity. (Both of the
following files are open.)

File 1: norlanco.xls - has data that I need to pull in

data is:
norlanco 123 321 312

File 2: Master_sheet.xls - where select data will be shown.

A1 is file reference - no concatenation for now, just typed in.
'[norlanco.xls]Sheet1'!A2:d3
{could single tick being taken as a justification be an issue?}
{Do I need double single ticks at beginning to counter?}

E1 is the lookup criteria: norlanco in this case - no single quotes

I want to look for norlanco within the file norlanco.xls and take the
2nd number in the VLOOKUP - 123.

This formula works:
=VLOOKUP(E1,[norlanco.xls]Sheet1!$A$1:$E$2,2,FALSE)

The application of the INDIRECT is what's throwing me off...



Everything that you have said and what is in the articles makes sense -
I hope that it is just a small format issue that I am not correctly
handling... Single quotes in the file reference maybe?


Again - any help is greatly appreciated...

Thank you!!


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

That did it! It took a couple tries, but that was the issue. Now I ca
put in the concatenation and tie to various files contingent on use
input. Truly appreciate the help - saves me quite a bit of time fo
redev. Thank you for taking time to chat with me..

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