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

If the workbook is closed, you'll need to use something like "INDIRECT.EXT"
to get the data.

http://xcell05.free.fr/english/moref...direct.ext.htm

=VLOOKUP(A1,INDIRECT.EXT("'C:\Data\[User
List.xls]Sheet1'!$A$1:$D$22"),4,FALSE)

I can't test it here because I don't have the addin loaded at home.

If you have very many cells and the external workbook is on a remote server
that takes a while to access, you may want to look at VBA.

--
HTH,
Barb Reinhardt



"Carl" wrote:

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

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

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

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