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
Extentions Synchronization
Deleted in Discussions on
3 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
38 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies