Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
using a cell reference is Vloopup to access external workbooks
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
using a cell reference is Vloopup to access external workbooks
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
using a cell reference is Vloopup to access external workbooks
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
using a cell reference is Vloopup to access external workbooks
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
using a cell reference is Vloopup to access external workbooks
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Reference to cell with external spreadsheet | Excel Worksheet Functions | |||
Reference external worksheet whose name is in a cell | Excel Worksheet Functions | |||
External Cell Reference in VBA? | Excel Programming | |||
dynamic external cell reference | Excel Worksheet Functions | |||
Using Cell Contents to Reference External Workbooks | Excel Programming |