Hi Alan
this probably won't work as (due to the path definition) the other
workbook seems to be closed - INDIRECT will return an error

--
Regards
Frank Kabel
Frankfurt, Germany

Alan Beban wrote:
Untested, but try substituting INDIRECT(A1) for A1.

Alan Beban

edsox5 < wrote:
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/