Including a cell value in the file name for vlookup

[rascough]

Hi there,

I am trying to add a cell value into a file name that is used for a vlookup, (although you probably figured that from my subject)

My current formula is
=VLOOKUP(C11,'[bottle plan 486.xls]plan'!$A$2:$E$31,3,FALSE)

However I would like to replace the 486.xls with the value entered in cell A1
=VLOOKUP(C11,'[bottle plan A1.xls]plan'!$A$2:$E$31,3,FALSE)

Bascially we might have 4 current bottle plans 486, 487, 488, 489 and the lookup value would change accordingly

Have tried a few variations of & and indirect but cant figure it out, would this need to be a macro?

once this works i might look at a similar formular to change from "bottle plan" to "can plan" based on a dropdown on cell value. but Im assuiming it would be the same approach


Regards
Richard

 

[R_Stephens]

Hi Richard

Try the following:

=VLOOKUP(C11,INDIRECT("'[bottle plan " & A1 & ".xls]plan'!$A$2:$E$31"),3,FALSE)

Hope it helps

Regards

Richard

 

[rascough]

does the file need to be opened for this to work?

 

[R_Stephens]

Unfortunately yes, the Indirect function requires that any target workbooks are open.