Hi Alan
just a 'good' guess on my side as he has defined the full path
information. If the other workbook is open this is not required.
But you're right, if the WB is open INDIRECT will work. I should have
stated that this was my assumption :-)

--
Regards
Frank Kabel
Frankfurt, Germany

Alan Beban wrote:
Did the OP indicate that the other workbook is closed?

Alan Beban

Frank Kabel wrote:
Hi
unfortunately neither INDIRECT nor INDIRECT.EXT (from the add-in
morefunc.xll) will work in this case as
- the other workbook is closed AND
- you need an array returned from the close workbook

Have a look at http://tinyurl.com/2c62u
for further alternatives. Note: They will probably be quite slow so
you may consider the following workaround:
- 'mirror' the cells with INDIRECT.EXT (also described in the link
from above) on a separate sheet in your workbook
- use VLOOKUP on this mirrored sheet
Though this could also be quite slow

Besides these formula solutions you may consider using VBA (though
this also has to access the closed workbook)


--
Regards
Frank Kabel
Frankfurt, Germany


This shouldn't be this difficult I feel!

I am trying to do a basic Vlookup, referencing an array and
returning a specific value. The trick is the location of the array
is in a different excel workbook, which is defined in a seperate
cell.

In cell A1 lies the path of the workbook as well as the sheet name

and

array info

A1='\\server\sharedfolder\[LCV.xls]QTR'!H:I

In cell A2 is the lookup, which searches for the value "25th" in
the
range defined in cell A1, and returns the value in the second
column.

A2=VLOOKUP("25th",A1,2,FALSE)

The problem is, this doesn't work, it won't accept what I have in
A1
as a range, I have tried messing with the single quotes and a few
other format type things in A1, but cannot get this to work.

This does work

=VLOOKUP("25th",'\\server\sharedfolder\[LCV.xls]QTR'!H:I,2,FALSE)

so I know my array is correct.

Please HELP!

Ed


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