Using a parameter in a path to another file

[whatever61]

I have the following formula referring to the file [MARKING 116E.xlsm]:

=VLOOKUP($C3;'[MARKING 116E.xlsm]YIELD'!$C$3:$I$4000;3)

I would like that '116E' will not be a fixed value, but will be taken from the cell A1.

Thanks

[Pete_UK]

You would normally use the INDIRECT function to do that, but INDIRECT will only work with open files, and I suspect that will not be the case here.

Pete

[whatever61]

You would normally use the INDIRECT function to do that, but INDIRECT will only work with open files, and I suspect that will not be the case here.

Pete

No, it's ok, it can be open.

Can you please integrate the INDIRECT in this formula? I can't find the syntax.
Thanks!

[Pete_UK]

Okay, try it this way:

=VLOOKUP($C3;INDIRECT("'[MARKING "&$A$1&".xlsm]YIELD'!$C$3:$I$4000");3)

(changes in red), where "116E" (without the quotes) is in A1.

Hope this helps.

Pete