How to write a formula that automatically updates a specific cell

@Paul Newcome Is a formula possible to achieve the following automation? I am trying to set up a workflow that when a due date+1day is reached and the status isn't complete that a cell update is triggered to update the symbol in the heath column to the red stop sign from the green.

Update the Health column to either a red, yellow, green, or gray circle if:

  • Status is future date= gray
  • Status is complete= green
  • Status is 'in progress' and the due data is in the future= green
  • The due date is passed but the completed date is blank OR the status is In process and it is past the due date= Red

Thank you so much for your help!


Best Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    @Sandee Murray There are a couple of issues with the last bit of your formula.

    First... The text is case sensitive. "RED" will output "RED", but "Red" will output the red ball.

    Next... Take a look at the quotes around "RED" in your formula vs the quotes around "RED" in my comment here. Notice how yours are slanted? Those are call "Smart Quotes" and Smartsheet doesn't like them (ironically enough). Rewrite the formula either directly in Smartsheet, here in the Community forum, or in a text editor such as Notepad (not Word).

    Finally... You closed the TODAY function, but you forgot to close the AND function which is the "logical statement" portion of the IF.

    Give this a go...

    =IF(Status@row = "Complete", "Green", IF(Due@row < TODAY(), "Red", IF(Status@row = "In Progress", "Green", "Gray")))

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    Answer ✓

    Hi @Sandee Murray

    I hope you're well and safe!

    Unfortunately, it's not possible to copy workflows between sheets, but it's an excellent idea!

    Please submit an Enhancement Request when you have a moment.

    Here's a possible workaround or workarounds

    • Save the sheet as new and modify it, but I'd assume it won't help because you've already created the other ones.

    Would that work/help?

    I hope that helps!

    Be safe and have a fantastic week!


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

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


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

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

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!