You'd want to use =indirect() to create that lookup range.

But =indirect() doesn't work when the sending workbook is closed.

That's why Barb suggested =indirect.ext() from Laurent Longre's addin.

Carl wrote:

Sorry Stefi/Barb,
A1 contains the workbook name, and B1 (for sake of argument) contains what
I'm looking up, so the vlookup should be something like
=VLOOKUP(B1,"'C:\Data\[" & A1 & ".xls]Sheet1'!$A$1:$D$22",4,FALSE), but
something's missing and I've no idea what.

Thanks for your quick responses!

"Stefi" wrote:

Your question is not quite clear! You wrote that cell A1 contains the name of
the workbook to be looked up (User list).

=VLOOKUP(A1,C:\Data\[User List.xls]Sheet1'!$A$1:$D$22,4,FALSE)
searches for the value of A1 (User list).

Do you want to find value "User list" in a workbook named User list?

Stefi



€žCarl€� ezt Ã*rta:

Hi, I don't know if this is possible without a vb script, but I'm trying to
use Vlookup to get data from an external workbook. However, the workbook name
is variable; e.g. cell A1 contains a workbook name (say User List) that
exists in folder c:/Data and the data range in that file is A1 to D22 column
D, so the vlookup expression would be =VLOOKUP(A1,C:\Data\[User
List.xls]Sheet1'!$A$1:$D$22,4,FALSE). However, if the workbook name changes
in cell A1, how can I make the formula pick up the data from the new workbook?

Many thanks for any help

--

Dave Peterson