vlookup with dynamic file path

[wilfrid147]

Hi there,

I am looking to perform a vlookup using an array from a closed file. However, since this formula will have to be used on a quarterly basis, I am looking to incorporate a dynamic element into the file path. Below is an example.

VLOOKUP($A2,'P:\invest\Attribution - FACTSET\Jun16\Quarter\[SFGLOBALGROWTHCOMP.xls]Sector'!$B$12:$V$3479,19,FALSE)

Since I will be doing this in 3 months again, ideally I would like to update the "Jun16" to "Sep16" which will match another file. Currently I am using

TEXT(NOW()-31,"mmm")&TEXT(NOW()-31,"yy")

to get a dynamic month and year name. However, I'm having trouble incorporating this into the file path using & without excel coming up with an error.

Your help is very much appreciated.


W

 

[wideboydixon]

=VLOOKUP($A2, INDIRECT("'P:\invest\Attribution - FACTSET\" & TEXT(NOW()-31,"mmm") & TEXT(NOW()-31,"yy") & "\Quarter\[SFGLOBALGROWTHCOMP.xls]Sector'!$B$12:$V$3479"),19,FALSE)

I think! Untested ...

WBD

 

[wilfrid147]

unfortunately doesn't work even with teh spaces taken out

 

[wideboydixon]

Can you step through the formula and see if the INDIRECT is getting the correct path?

WBD

 

[wilfrid147]

Can you step through the formula and see if the INDIRECT is getting the correct path?

WBD

No it is not. There concatenation just isn't working the way we intend it to.

 

[wideboydixon]

I just read an article on how you can't use INDIRECT with external workbooks. Seems that this approach won't work anyway. Sorry. You're into UDF territory.

WBD