PDA

View Full Version : How to use the path of an excel filevariable in a formula?



prabhafriend
01-07-2015, 06:26 AM
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

Kenneth Hobs
01-07-2015, 08:07 AM
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.