External Reference in Vlookup

[C Brandt]

Hi Guys:

I would like to do a vlookup from an external reference. I've done this many
times where the file name of the external reference is known at the time of
developing the macro. This time, the file name will vary from instance to
instance, but the internal structure of the file will be identical and I can
insist that the user keeps the file in the same location as this
spreadsheet.
My first attempt was to develop the vlookup formula on-the-fly using a cell
where the user input the external reference file name. While this worked the
first time, it failed the repeat test.
This sounds like it should be fairly simple, but the procedure excapes me.

Any thoughts,

Craig

 

[Guest]

in what way did the external reference fail?
The indirect function should work in the reference area
additionally
a cell which will be used to gather the external reference could be used as
the reference for the lookup, it would also be easy to see if the reference
came in properly.

 

[C Brandt]

The code was generated some time ago and I'm not sure exactly how I did it,
but the code froze on the target file and never changed when the user chaged
the cell holding the target filename. Big opps.
Can you give me an example of the indirect method that will apply?

Craig

 

[Guest]

if, for example you want to lookup in the range A1:G20 in a worksheet in
different books
put the name of the workbookin a cell (A1?)
put the name of the worksheet in a Cell (B1?)

=vlookup(Lookup_reference,offset(indirect("'["&A1&"]"&B1&"'!A1"),0,0,20,8),ref_column,0)

 

[C Brandt]

bj:

I seem to be missing something: What is the "Offset" portion of the formula
all about?

Here is the formula that I am trying to generate:
=VLOOKUP($H12,'[Trades Sheet - 6-28-07.xls]Buy'!$A$13:$BV$500,70,0) '
This works.

Cell I4 is equal to the filename ( Trades Sheet - 6-28-07a.xls )
Cell I5 is equal to the range ( Buy'!$A$13:$BV$500 ) ' I could build the
range into the formula since it shouldn't change.
But, looking at your example and with a little work on my part, I figured
the new formula should look like this:
=VLOOKUP($H12,indirect("'["&$I$4&"]"&$I$5),70,0)
I will populate the rest of the row with this, then copy and paste-value
over the formulas to lock the data into place.
Unfortunately, this formula gives me a #REF! error.

Any ideas?

Thanks for your help. I'm way over my head and without this type of help I
would unquestionably drown,
Craig

if, for example you want to lookup in the range A1:G20 in a worksheet in
different books
put the name of the workbookin a cell (A1?)
put the name of the worksheet in a Cell (B1?)

=vlookup(Lookup_reference,offset(indirect("'["&A1&"]"&B1&"'!A1"),0,0,20,8),r
ef_column,0)

The code was generated some time ago and I'm not sure exactly how I did it,
but the code froze on the target file and never changed when the user chaged
the cell holding the target filename. Big opps.
Can you give me an example of the indirect method that will apply?

Craig

used
as this
many time
of instance
to and I
can a
cell worked
the excapes
me.