using a cell reference is Vloopup to access external workbooks

[Guest]

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

 

[Guest]

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

http://xcell05.free.fr/english/morefunc/indirect.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.

 

[Guest]

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:

 

[Guest]

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!

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]

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.

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!

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

 

[Guest]

Thanks for all your help everyone (sorry Barb - I didn't even notice the
link!). I've downloaded the addins, but restrictions on my office PC mean
that I can't install them - looks like it's going to be vba after all.

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.

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!

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]

You could retrieve values from a closed workbook using a routine from John
Walkenbach:
http://j-walk.com/ss/excel/eee/eee009.txt
Look for either: GetDataFromClosedFile or GetValue.

It may be easier to create a worksheet (hidden) and populate a cell on that
worksheet with the formula you need--no =indirect()'s--you'd have to use the
real drive, folder, workbook, worksheet names.

Thanks for all your help everyone (sorry Barb - I didn't even notice the
link!). I've downloaded the addins, but restrictions on my office PC mean
that I can't install them - looks like it's going to be vba after all.

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.

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!

:

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

 

[Guest]

Here is another opinion:
Since the source workbook is closed it would be highly unlikely that you
would need an formula such as Dave suggests. Code would be much faster in
the long run.

Something like this in the ThisWorkbook module would run when the workbook
is opened if security settings allow it and the user doesn't disable macros.

Private Sub Workbook_Open()
Dim s as String
s = "=VLOOKUP(B1,'C:\Data\[ZZZ.xls]Sheet1'!$A$1:$D$22,4,FALSE)"
with worksheets("Sheet1")
.Range("B1").formula = Replace(s,"ZZZ",.Range("A1"))
End with
end sub

Also, the code Dave suggests is set up like a straigh link to a specific
cell - not a vlookup. I have altered it in the past to do a vlookup, but I
would go with the event code.



--
Regards,
Tom Ogilvy

You could retrieve values from a closed workbook using a routine from John
Walkenbach:
http://j-walk.com/ss/excel/eee/eee009.txt
Look for either: GetDataFromClosedFile or GetValue.

It may be easier to create a worksheet (hidden) and populate a cell on that
worksheet with the formula you need--no =indirect()'s--you'd have to use the
real drive, folder, workbook, worksheet names.

Thanks for all your help everyone (sorry Barb - I didn't even notice the
link!). I've downloaded the addins, but restrictions on my office PC mean
that I can't install them - looks like it's going to be vba after all.

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!

:

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]

I think that you enhanced this portion of my response.

It may be easier to create a worksheet (hidden) and populate a cell on that
worksheet with the formula you need--no =indirect()'s--you'd have to use the
real drive, folder, workbook, worksheet names.

Here is another opinion:
Since the source workbook is closed it would be highly unlikely that you
would need an formula such as Dave suggests. Code would be much faster in
the long run.

Something like this in the ThisWorkbook module would run when the workbook
is opened if security settings allow it and the user doesn't disable macros.

Private Sub Workbook_Open()
Dim s as String
s = "=VLOOKUP(B1,'C:\Data\[ZZZ.xls]Sheet1'!$A$1:$D$22,4,FALSE)"
with worksheets("Sheet1")
.Range("B1").formula = Replace(s,"ZZZ",.Range("A1"))
End with
end sub

Also, the code Dave suggests is set up like a straigh link to a specific
cell - not a vlookup. I have altered it in the past to do a vlookup, but I
would go with the event code.

--
Regards,
Tom Ogilvy

You could retrieve values from a closed workbook using a routine from John
Walkenbach:
http://j-walk.com/ss/excel/eee/eee009.txt
Look for either: GetDataFromClosedFile or GetValue.

It may be easier to create a worksheet (hidden) and populate a cell on that
worksheet with the formula you need--no =indirect()'s--you'd have to use the
real drive, folder, workbook, worksheet names.

Thanks for all your help everyone (sorry Barb - I didn't even notice the
link!). I've downloaded the addins, but restrictions on my office PC mean
that I can't install them - looks like it's going to be vba after all.

:

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!

:

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

 

[Guest]

Nice one guys - thanks for all your help on this one. VBA's the route I've
taken.

I think that you enhanced this portion of my response.

It may be easier to create a worksheet (hidden) and populate a cell on that
worksheet with the formula you need--no =indirect()'s--you'd have to use the
real drive, folder, workbook, worksheet names.

Here is another opinion:
Since the source workbook is closed it would be highly unlikely that you
would need an formula such as Dave suggests. Code would be much faster in
the long run.

Something like this in the ThisWorkbook module would run when the workbook
is opened if security settings allow it and the user doesn't disable macros.

Private Sub Workbook_Open()
Dim s as String
s = "=VLOOKUP(B1,'C:\Data\[ZZZ.xls]Sheet1'!$A$1:$D$22,4,FALSE)"
with worksheets("Sheet1")
.Range("B1").formula = Replace(s,"ZZZ",.Range("A1"))
End with
end sub

Also, the code Dave suggests is set up like a straigh link to a specific
cell - not a vlookup. I have altered it in the past to do a vlookup, but I
would go with the event code.

--
Regards,
Tom Ogilvy

You could retrieve values from a closed workbook using a routine from John
Walkenbach:
http://j-walk.com/ss/excel/eee/eee009.txt
Look for either: GetDataFromClosedFile or GetValue.

It may be easier to create a worksheet (hidden) and populate a cell on that
worksheet with the formula you need--no =indirect()'s--you'd have to use the
real drive, folder, workbook, worksheet names.



Carl wrote:

Thanks for all your help everyone (sorry Barb - I didn't even notice the
link!). I've downloaded the addins, but restrictions on my office PC mean
that I can't install them - looks like it's going to be vba after all.

:

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!

:

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