variable part in function

New 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!


Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
16 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
11 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies