| 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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
using a cell reference is Vloopup to access external workbooks
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. "Dave Peterson" wrote: 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 -- Dave Peterson |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
using a cell reference is Vloopup to access external workbooks
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 "Dave Peterson" wrote: 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. "Dave Peterson" wrote: 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 -- Dave Peterson |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
using a cell reference is Vloopup to access external workbooks
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. Tom Ogilvy wrote: 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 "Dave Peterson" wrote: 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. "Dave Peterson" wrote: 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 -- Dave Peterson -- Dave Peterson |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
using a cell reference is Vloopup to access external workbooks
Nice one guys - thanks for all your help on this one. VBA's the route I've
taken. "Dave Peterson" wrote: 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. Tom Ogilvy wrote: 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 "Dave Peterson" wrote: 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. "Dave Peterson" wrote: 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 -- Dave Peterson -- 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 | |||
![Carl[_3_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Linear Mode
