variable part in function

Copper Contributor

Dear people,

can somebody help me with the following issue, since my tries are not leading to the solution.

 

I am using a reference cell from another file, which has a week number in its filename, so this changes every week. The week after my reference cells will not work anymore.

Now I would like to make this weekpart variable, so this would make it easy for me to change this information on 1 place, instead of changing all the reference cells. Below there is just 1 cell, but I need 36 cells. Otherwise I have to update 36 cells every week by hand.

 

I am trying to use:

=INDIRECT("='/Volumes/Management/Rapportage/2019/[OHW 2019 " & B3 & ".xlsx]Samenvatting2019'!$T$26")

and in B3 I put in week14 to get

='/Volumes/Management/Rapportage/2019/[OHW 2019 2014.xlsx]Samenvatting2019'!$T$26

 

Why is my indirect not working? or do I have to use another function? 

Macro's could be an option, but that is quite a while ago that I have worked with that and INDIRECT looks easy to use.

thanks in advance!

2 Replies
INDIRECT requires the linked file to be open in Excel. I would use the fixed link and then go into the Data, Edit Links dialog to change the link.

@Jan Karel Pieterse 

Ah that is the case. The other file was not open, but I will take a look into the suggestion you made Data, Edit links dialog. Don't know much about this, but I will first look into this myself. Thank you for your response!