dynamic links to other workbooks

[vladutzferoviarul]

Hello all,


I want to ask you if it's possible to have link addresses that can change due to the values found in a cell?
For example, I have a lot of similar reports in different folders on drive D, depending on date.

For 14th of November, I have: D:\Reports\2016\11\14\20161114.xls
For 15th of November, I have: D:\Reports\2016\11\15\20161115.xls

Then, in my workbook where I want to import all the results, I have in column A the date in format yyyymmdd. In column B I would like to create a dynamic link which can import me the value from cell A1 from each report file depending on date.

I tried and it's not working like this, but for sure you will understand what I want to do:

B1='D:\Reports\LEFT(A1;4)\MID(A1;5;2)\RIGHT(A1;2)\LEFT(A1;4)&MID(A1;5;2)&RIGHT(A1;2).xlsx]Sheet1'!$A$1

Do you know if there's any possibility to do something like this to work? Thank you.

Vlad
[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]20161114
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]20161115
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Mark80]

Hi,

I believe you will need to use the INDIRECT function. Something like the following should work:

=INDIRECT("D:\Reports"&LEFT(A1,4)&""&MID(A1,5,2)&""&RIGHT(A1,2)&""&LEFT(A1,4)&MID(A1,5,2)&RIGHT(A1,2)&".xlsx]Sheet1'!$A$1")

 

[akima]

Is there any other to do this as it really slows down calculations. Please help