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.
>Solution :
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.