Current year formula

02/05/21
Answered - Pending Review

Hi Everyone,


I currently have a sheet that populates mostly 2020 data. But we now would like to start integrating reports for 2021. Every individual formula has criteria for 2020, which I will now have to change 1 by 1 to 2021.

I have an old excel sheet that allows me to change the year in 1 single cell, and then all formulas will change to report for that given year. Is there a way to do this in smartsheet?

Example of a formula I am currently using:

=COUNTIFS({Environmental Incident Tracking Range 3}, "1", {Environmental Incident Tracking Range 1}, "Leak / Fuite", {Environmental Incident Tracking Range 7}, =(2020))

Snip of the sheet:

TIA

Answers

  • Andrée StaråAndrée Starå ✭✭✭✭✭

    Hi @Jessica Howell

    I hope you're well and safe!

    Yes, you can reference a specific cell in the formulas. Either in the sheet or the Sheet Summary.

    Would that work/help?

    I hope that helps!

    Be safe and have a fantastic weekend!

    Best,

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

    SMARTSHEET PARTNER & CONSULTANT / EXPERT

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E: [email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

  • edited 02/05/21

    Hi Andree!


    @Andrée Starå That would definitely work! Any suggestions formula wise?

  • @Andrée Starå Hi Andree, any suggestions?


    Thank you

  • Andrée StaråAndrée Starå ✭✭✭✭✭
    edited 02/17/21

    @Jessica Howell

    Yes, sorry about the delayed response.

    It would look something like this.

    Add a sheet summary field called Year that you'd then reference in the formula.

    =COUNTIFS({Environmental Incident Tracking Range 3}, "1", {Environmental Incident Tracking Range 1}, "Leak / Fuite", {Environmental Incident Tracking Range 7}, =Year#)

    Make sense?

    Did it work?

    Remember! Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

    SMARTSHEET PARTNER & CONSULTANT / EXPERT

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E: [email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

  • @Andrée Starå Thank you!

    How does the formula match between the sheets

    (Sheet i want the data populated on) added year cell

    (Sheet the data is being pulled from)

    Every cell with the blue mark on (SC- Refrigerant leak data) uses a formula very close to:

    =COUNTIFS({Environmental Incident Tracking Range 3}, "1", {Environmental Incident Tracking Range 1}, "Leak / Fuite", {Environmental Incident Tracking Range 7}, =(2020))


    I think I need a new formula that pulls the same data but if the pink cell is 2020, then it only pulls 2020 data. If 2021, only pulls data from this year, etc. from the other sheet

    Like a match formula for the year columns?

    The above formula you provided comes up unparseable.


    Thoughts?

  • Andrée StaråAndrée Starå ✭✭✭✭✭

    @Jessica Howell

    You're more than welcome!

    I'd be happy to take a quick look.

    Can you maybe share the sheet(s)/copies of the sheet(s)? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, [email protected])

    Remember! Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

    SMARTSHEET PARTNER & CONSULTANT / EXPERT

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E: [email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

  • @Andrée Starå

    made 2 new empty sheets in the same format, and shared with you

    Thank you for your help!

Sign In or Register to comment.