SamC
0
I'm trying to dynamically reference a cell that holds a date which I want to increase by one.
=IFERROR(INDEX(INDIRECT("'["&$EU$1&".xlsx]"&$A$1&" "&EU2&"'!$AF:$AF"),
MATCH($E3,INDIRECT("'["&$EU$1&".xlsx]"&$A$1&" "&EU2&"'!$B:$B"),0)),"N/A")
In this instance $A$1 hold the date, 2021, which I would like to increment by one to 2022.
Is there a way to accomplish this in the formula?
i.e.
..."&SUM($A$1+1)&"...
Thanx for looking, Sam
I think you've overcomplicated the solution with the sum approach.
It's simply changing $A$1 in your formula to $A$1+1. If $A$1 contains 2021, the value being concatenated into your strings will be 2022:
=IFERROR(INDEX(INDIRECT("'["&$EU$1&".xlsx]"&$A$1+1&" "&EU2&"'!$AF:$AF"), MATCH($E3,INDIRECT("'["&$EU$1&".xlsx]"&$A$1+1&" "&EU2&"'!$B:$B"),0)),"N/A")
This of course assumes that A1 contains specifically a year in the form 2021 and not an excel serial date value.