Vlookup With Indirect

[Thomas23]

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

 

[steve]

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

 

[Tom Ogilvy]

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.

 

[Thomas23]

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!

 

[Thomas23]

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!

 

[Tom Ogilvy]

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.

 

[Thomas23]

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..