Forum Discussion

JulianaD's avatar
JulianaD
Copper Contributor
Apr 03, 2019

variable part in function

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!

excel
Formulas and Functions

2 Replies

  • JKPieterse's avatar
    JKPieterse
    Silver Contributor
    Apr 03, 2019
    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.
    • JulianaD's avatar
      JulianaD
      Copper Contributor
      Apr 03, 2019

      JKPieterse 

      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!

       

Resources