Use a reference cell for the file name in a path

[Berna11]

Good evening as I would like to know if it is possible to use a cell for the reference of the name on a path of a formula.
As an example, the formula contains this path:

.... ($ B5; 'C: \ Users \ john \ Desktop \ New folder \ [prova.xlsx] Sheet1'! ....

put in the A1 prova.xlsx cell and in the formula put A1 type:

.... ($ B5; 'C: \ Users \ john \ Desktop \ New folder \
A1] Sheet1'! ....

in red the variation I would like to do if possible
Thank you.

[Pete_UK]

You would normally do that using the INDIRECT function, but this function does not work on closed workbooks.

An alternative that you could try is to find and download the free add-in called morefunc (do a Google search for sites where you can get it). This add-in includes a function INDIRECT.EXT, which does work on closed workbooks, and I have seen comments on here that it works in XL2010.

Hope this helps.

Pete

[Berna11]

Thanks in the meantime for the answer.
If I use the INDIRECT function with the file open,
how do I change the formula syntax?
Thanks again

[Pete_UK]

Assume A2 contains this text to define the path:

C: \ Users \ john \ Desktop \ New folder \

and A1 contains the filename:

prova.xlsx

then if you wanted to sum the cells X1:Y7 in Sheet1 of that file then you would use this formula:

=SUM(INDIRECT("'" & A2 & "[" & A1 &"]Sheet1'!X1:Y7"))

It won't work if the file is closed, but if the file is open you should get your result. Note that you have to explicitly include apostophes, square brackets, even ".xlsx", if these are not present in the text values.

Hope this helps.

Pete

[Berna11]

Forgive me Pete with sum is fine
= SUM (INDIRECT ("'" & A2 & "[" & A1 & "] Sheet1'! E3: E5"))

but, with VLOOKUP it does not work what is missing?

= VLOOKUP (B5; (INDIRECT ("'" & A2 & "[" & A1 & "] Sheet1'! $ A $ 3: $ E $ 5; 5; 0"))

incorrect brackets or apostrophes? I can not I can not give enter

[Pete_UK]

The INDIRECT is for the table references, so try it like this:

= VLOOKUP (B5; INDIRECT("'" & A2 & "[" & A1 & "] Sheet1'! $ A $ 3: $ E $ 5") ; 5; 0)

Hope this helps.

Pete

[Berna11]

OK perfect
Thank you