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/