Forum Discussion

Barbara Mercik's avatar
Barbara Mercik
Copper Contributor
Apr 05, 2018

how do I change the year in an external file reference formula

My formula refers to another Excel file, Schedule 2017, to add to Schedule 2018. How do I change the file reference in the formula to YEAR-1 instead of having to manually change the formula file reference to an actual number for each new year?

Formulas & Functions
tips and tricks
  • Barbara Mercik's avatar
    Barbara Mercik
    Copper Contributor
    Apr 05, 2018

    I think I answered my own question. After several hours, I came up with this.


    =INDIRECT(TEXT("'[Closing_Schedule "&(YEAR(TODAY())-1)&" v1.xlsx]Closing Schedule'!$L$4",1))


    Thoughts and comments please.

Resources