Fomulas and automation

Andyw_999

Hi Everyone.

I have a rag status column on my Smartsheet which is determined by a formula, so as the task becomes due or overdue the rag status updates from green→amber→red.

I'd like to receive a notification as tasks transition to amber/red. I created an automation, based on the change of rag status, but it only triggers if someone opens the smartsheet which updates the formula and triggers the automation.

Is there a way I can get the automation to trigger the formula update and send the email with out relying on someone opening the sheet?

Thanks for any help

Paul Newcome

What is your current formula? It almost sounds as if you are using the TODAY function. This particualr function doesn't update on the back-end until the sheet is activated. You can activate the sheet automatically to update this function though by inserting a date type column (can be hidden to reduce clutter). Then you would set up a Record A Date automation set to run every morning at 12:00am in this new date column.

Andyw_999

Thanks Paul!

You're spot on

=IF(Status3 = "Complete", "Blue", IF(Finish3 < (TODAY(0) + 1), "Red", IF(Finish3 < (TODAY(0) + 7), "Yellow", "Green")))

I'm just about following your suggestion, just the Record A Date automation. Also would I then replace my today() references to be the new helper column?

Thanks again,

Andy

Paul Newcome

You would leave your TODAY() reference as is. The ONLY reason for the record a date setup is to force refresh the TODAY function.