I use calc for household expenses each year using multiple sheets (2024, 2025, 2026, etc) in one file.
Is it possible to have formulas use the year in one cell to change any formulas to the present year?
For Example:
Cell A2 has the past year, 2025.
For 2026, I have multiple formulas to compare costs from previous year, such as =IF(E7>0,E7-($'2025'.E7),0).
Can I somehow have =IF(E7>0,E7-($'A2'.E7),0) so that all formulas would update by changing just the value of one cell (A2)?
If the sheet name is the year, one of these formulas will let you extract the year from the sheet name:
A1: =REGEX(A3;"^.+#\$(.*)";"$1";"g")
=MID(CELL("filename");FIND("#$";CELL("filename"))+2;99)
=MID(CELL("filename");FIND("#$";CELL("filename"))+2;LEN(CELL("filename")))
To retrieve the value of a cell from the previous year:
=INDIRECT(($A$1-1)&"."&CELL("address";A1))
Using CELL inside INDIRECT makes possible to copy the formula with the address adapted to the new cell.