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
Which formula to use
Ramon Haagen in Excel on
2 Replies
function talking to table storage
donquijote in Compute on
0 Replies
Calculated column help
gopalaraoa in SharePoint on
1 Replies
Combining IF and INDEX functions
Hans van Deursen in Windows PowerShell on
2 Replies
Finding the data and to color code it
NishantSalunkhe in Excel on
3 Replies