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

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" wrote:

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

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

"bj" wrote in message
...
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" wrote:

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

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" wrote:

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

"bj" wrote in message
...
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" wrote:

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

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

"bj" wrote in message
...

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)

"C Brandt" wrote:

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

"bj" wrote in message
...
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" wrote:

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