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

Options

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!

Tags:

Best Answer

  • J Tech
    J Tech ✭✭✭✭✭
    Answer ✓
    Options

    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.

    Regards

    J Tech

    If my response has helped you in any way or provided a solution to your problem, please consider supporting the community by marking it as Insightful, Vote Up, or Awesome. Additionally, you can mark it as the accepted answer, which will make it easier for others to find a solution or get help with a similar issue in the future. Your support is greatly appreciated!

Answers

  • J Tech
    J Tech ✭✭✭✭✭
    Answer ✓
    Options

    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.

    Regards

    J Tech

    If my response has helped you in any way or provided a solution to your problem, please consider supporting the community by marking it as Insightful, Vote Up, or Awesome. Additionally, you can mark it as the accepted answer, which will make it easier for others to find a solution or get help with a similar issue in the future. Your support is greatly appreciated!
  • Miranda F.
    Options

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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!