Can I use a helper cell to house the year in these formulas?

Miranda F.

I'm wondering if I can have a helper column/cell that houses the year instead of including 2023 in all of my formulas (we're talking over a thousand cells). I want to have a helper cell that has 2023 (or 1/1/2023 if it needs to be date format), so that when it comes time to update everything at year-end, I can just change that one cell to 2024 instead updating ALL of my formulas again.

These are a few of the formulas I'm using:

=COUNTIFS(Client:Client, "Client", [Report Date]:[Report Date], YEAR(@cell) = 2023)

=COUNTIFS(Client:Client, "Client", [Award Date]:[Award Date], IFERROR(YEAR(@cell), "") = 2023)

=SUMIFS(Amount:Amount, Client:Client, "Client", [Report Date]:[Report Date], YEAR(@cell) = 2023)

=SUMIFS(Amount:Amount, Client:Client, "Client", [Award Date]:[Award Date], IFERROR(YEAR(@cell), "") = 2023)

=SUMIFS(Amount:Amount, Team:Team, "Cx", [Report Date]:[Report Date], WEEKNUMBER(@cell) = 1, [Report Date]:[Report Date], YEAR(@cell) = 2023)

Any help is appreciated, thank you!

J Tech

Hi Miranda,

The easiest way to do this would be to create the Year field in the sheet summary and replace the 2023 in your formulas to be the sheet summary field.

J Tech

Hi Miranda,

The easiest way to do this would be to create the Year field in the sheet summary and replace the 2023 in your formulas to be the sheet summary field.

Miranda F.

😲 Oh man, I wish I had asked this like a week ago! Thank you!!