Reference Excel files getting filename from a cell

Question

I want to get values from different excel files (say, File-AAA.xlsx, File-ABC.xlsx, etc). ¿How can I reference said files without inputting manually the "ABC" part of the filename, but getting it from the current sheet instead?

In the example, from our current sheet we want to get the values of A1 and A2, from the File-ABC.xlsx file, and sum them.

For instance, in B2, instead of writing:

    =SUM('\\server\path\[File-ABC.xlsx]Data'!A1:A2)

I would like to be able to write it as

    =SUM('\\server\path\[File-*REFERENCE TO CELL*.xlsx]Data'!A1:A2)

Where *REFERENCE TO CELL* is the sought text string that references the cell that contains "ABC" (A2).

Answer 1

You can use the indirect function for this. Something like this -

=SUM(INDIRECT("'\\server\path\[File-"&A2&".xlsx]Data'!A1"&":"&"A2"))

Edit-1 : You can use the same syntax to refer to the local directory that you want, just replace \server\path[File- with the path of the new directory. You can copy the path from your file explorer after browsing to the concerned folder.

Eg. Taking the path from your comment (after correction):

=SUM(INDIRECT("'C:\Users\PeteThePanda\Spreadsheets[File-"&A3&".xlsx]Data'!A1"&‌​":"&"A2"))

Answer 2

A more straightforward way to do this is set up a conditional statement outside the sum, e.g. if the value of A2 in the current workbook is ABC, then go to this path and bring in the sum.

e.g.

=if(A2="ABC",sum('\\server\path\[File-ABC.xlsx]Data'!A1:A2),if(A2="AAA", sum('\\server\path\[File-AAA.xlsx]Data'!A1:A2),"No File"))

Note, you can add additional nested IFs if there are more files. Just start a new if where I wrote "No File".