how to reference different sheets in a formula depending on current date-CodePudding

I have multiple sheets of iterative data based on years (2022, 2021, 2020, etc). I want to write formulas that pull rolling 12 months of data.

For example I have the following equation currently:

=IF($D27="",0,IF($C35="PY",VLOOKUP(D30,'2021'!$A$15:$M$209,$B35,FALSE),VLOOKUP(D30,'2022'!$A$15:$M$209,$B35,FALSE)))

I am trying to make the sheet reference of '2021' as '=year(today())-1' and '2022' as 'year(today())' like this:

=IF($D27="",0,IF($C35="PY",VLOOKUP(D30,'=year(today())-1'!$A$15:$M$209,$B35,FALSE),VLOOKUP(D30,'year(today())'!$A$15:$M$209,$B35,FALSE)))

Is there a way to do this or is there a more effective approach.

CodePudding user response：

You will need to use INDIRECT that can take a string an make it a viable range reference:

=IF($D27="",0,IF($C35="PY",VLOOKUP(D30,INDIRECT("'"&year(today())-1&"'!$A$15:$M$209"),$B35,FALSE),VLOOKUP(D30,INDIRECT("'"&year(today())&"'!$A$15:$M$209"),$B35,FALSE)))

One note: INDIRECT is Volatile, which means it will recalc anytime Excel Recalcs. And it will return #REF if the workbook to which it is pointed is closed.