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

[prabhafriend]

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-q...e-formula.html

[Kenneth Hobs]

In forums like this, you can attach a file. Zip and attach two simple files to help us help you more readily.

Offhand, I think you mean a Named cell rather than filevariable. Have you tried looking at Indirect() and use string concatenation operator & to build the string?

When using Indirect() like that, that file will need to be open. It might be better to use a macro to allow more automatic updates.