To Link Excel Books using cells with text

[cvazquez]

Dear all,

I need to link the values of several excel books to a main excel workbook. The process using the copy-paste function with all the excel sheets opened is simple, however, when I try to link the values by hand (using the file domain) it doesn't work. The problem comes from a failed syntax when I replace the original domain between the single quotes with a cell with the "text" of that domain.

For example:

-Original

='C:\CVR\Folder1\Folder1-1\Folder1-1-1\[ResultsExcel_3Dv11.xlsx]CV Average Temperature'!$B2

-Fail case

='[A1]'!$B2

being the A1 value=C:\CVR\Folder1\Folder1-1\Folder1-1-1\[ResultsExcel_3Dv11.xlsx]CV Average Temperature

I am trying to do this process because I need to do some changes in the links to the different excel workbooks such the ones that you can see in the attached example table

Thank you in advance for your answers and kind regards,

Charlie

[#2 · Accepted solution]

@cvazquez 

In general it'll be like

=INDIRECT("'"&A8&"'!$B2")

but the file you linked shall be opened, INDIRECT doesn't work with closed files.

[#3]

@Sergei Baklan 

It works perfectly! Thank you very much,

Charlie

[#4]

@cvazquez 

Charlie, you are welcome