How to use the path of an excel filevariable in a formula? (1 Viewer)

namliam

The Mailman - AWF VIP
Local time
Today, 06:26
Joined
Aug 11, 2003
Messages
11,696
Not really sure if that is at all possible :(

Perhaps if you use named ranges, that you can set and use that named range as you lookup table? Not sure if that works either though
 

NBVC

Only trying to help
Local time
Today, 01:26
Joined
Apr 25, 2008
Messages
317
If you mean the path, workbook name and sheet name or any combination thereof is stored in specific cells in the sheet and you want to reference those to get the info required, then you can employ the INDIRECT function.

e.g. this assumes the path (including the drive letter and the backslashes), the workbook name and the sheet name are in cells X1:Z1, respectively, then try:

=VLOOKUP(A1,INDIRECT("'"&X1&"["&Y1&".xls]"&Z1&"'!$A:$K"),11,FALSE)

Note: I included the .xls extension in the formula so it is not needed in Y1, but if you do include it in Y1, then remove from formula.


NOTE: I messed that up a bit. The Excel INDIRECT() function does not work when referencing closed workbooks. Instead you can use a VBA function (see here: PULL() Function). It is used like the INDIRECT() function... so replace INDIRECT with PULL in the above formula after you installed the user defined function
 
Last edited:

Users who are viewing this thread

Top Bottom