How to write a formula that automatically updates a specific cell

Sandee Murray

@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!

Sandee

Paul Newcome

@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å

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!

Best,

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!

hollyconradsmith

Hi Sandee,

Did you try using the update cell value automation? This will probably be easier than a formula. https://help.smartsheet.com/articles/2482299-change-cell-value-in-an-automated-workflow

You can also try a nested if formula like this =if(status@row="future date", "gray", if(status@row="complete", "green", if(and(status@row="in progress", [due date]@row<today()), "green", "red")))

I didn't build a sheet to completely test that formula, but it should be pretty close. You would probably want to throw an iferror in front of that.

Sandee Murray

@hollyconradsmith I tried the automation first, but it wouldnt let me update the health cell with the colored symbols.

I have been trying to create a formula, but no luck yet : )

Sandee Murray

@Paul Newcome I am so close! I just can't get the last piece: IF(and(Status12 =”In process”,due12<TODAY(),”RED”,”PURPLE”)), to work. Any ideas?!?

=IF(Status12 = "complete", "Green", IF(Status12 = "Future Task", "Gray", IF(and(Status12 =”In process”,due12<TODAY(),”RED”,”PURPLE”))

Bassam Khalil

Hi @Sandee Murray 

Hope you are fine, please try the following formula ( i didn't find the condition when the health column must be yellow )

=IFERROR(IF(OR(Status@row = "complete", AND(Status@row = "in progress", [Due Date]@row > 
TODAY())), "Green", IF(AND(Status@row = "in progress", [Due Date]@row < TODAY(), 
ISBLANK([Completed Date]@row)), "Red", IF(Status@row = "Future Task", "Gray"))), "")

the following screenshot shows the result:

Paul Newcome

@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")))

Sandee Murray

@Paul Newcome Thank you so much! The formula works perfectly! Any idea if there is an easy way to move a automation created on one sheet to others with out having to rebuild it indiviually on other sheets?

Andrée Starå

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!

Best,

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!

Paul Newcome

@Sandee Murray Happy to help. 👍️

Please see Andree's response regarding your question about copying automations. Unfortunately it is not currently possible.

Sandee Murray

@Paul Newcome - I saw that, I sent in a request for the functionality. Is there a place that lists request so i can vote them up or down?

Paul Newcome

There is not. Smartsheet likes to keep their roadmap under wraps. Maybe another enhancement request? Haha

Sandee Murray

Well that is silly! I will add that enhancement request too!

Sandee Murray

@Andrée Starå Yeah my work around will be to 'save as' a current plan I have and then make it a basic project plan. From there I will make it a template and always start from there : )