Pass worksheet name as a variable in the formula bar BUT in a link to another file!?

[pirdop]

Hello all,
how I can pass a worksheet name as a variable in a link to another workbook?

here is what I m trying to achieve: I have 2 workbooks Summary.xlsx and Data.xlsx
in the DATA workbook I have 2 worksheets: CARS and TRUCKS

now I want in function of what is in cell A1, A2, etc in the Summary file the links to automatically refer to the CARS/TRUCKS worksheet

in a single workbook the

INDIRECT(ADDRESS(2,2,,,A3))

combination works perfectly, but I cannot make it work with external workbooks. the variable(Sheetname) is stored in A3.

let's say that the absolute link link to the CARS sheet is:

[B]C:\Test\[Data.xlsx]CARS'!$B$2[/B]

so what I need is to change somehow the link to TRUCKS!$B$2.


thank you all

 

[Andrew Poulsom]

That would be:

=INDIRECT("[Data.xlsx]TRUCKS"&ADDRESS(2,2,,,A3))

but Data.xlsx must be open.

 

[pirdop]

Thank you Andrew,

=INDIRECT("[Data.xlsx]"&ADDRESS(2,2,,,A3))

as indicated this works, I just removed the TRUCKS part from your formula. It is stored in the A3 cell and changes CARS, TRUCKS etc...

any possibility to make it work as a link i.e. no need to have DATA.xlsx opened?

 

[pirdop]

PS. the above doesn't seem to work with worksheet names containing spaces. !? given that in the cell a3 the string is absolutely the same

 

[Andrew Poulsom]

Try:

=INDIRECT("'[Data.xlsx]"&A3&"'!"&ADDRESS(2,2))