How to use the path of an excel filevariable in a formula?

[prabha_friend]

Regular Formula:
=VLOOKUP(A1,'SomeDrive:\SomeFolder\SomeSubFolder\[SomeFileName.xlsx]SomeSheetName'!$A:$K,11,FALSE)

The Formula I need:
=Vlookup(A1,GetPathofFileVariable(SomeFileVariable) & SomeSheetname!$A:$K,11,FALSE)

Got No Replies Here:
http://www.mrexcel.com/forum/excel-questions/827031-how-use-path-excel-filevariable-formula.html

 

[namliam]

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]

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