Formula to join three different options to provide a status

Hi, I am looking to join three different columns to provide the current status (Red, Yellow, Green, Gray). I have tried a few different formulas but can not seem to join them together. Below are the separate formulas that work, but I not able to join the formula together at this stage.
=IF(NOT(ISBLANK([Cleaning booked in]@row)), "Yellow", "Red")
=IF(NOT(ISBLANK([Tool Box Time & Date]@row)), "Green")
=IF([COVID Restrictions Now Lifted]@row, "Gray")
So I am looking at the formula to look at three different locations to give me back The status, the status flow is
Newline created = Red
The date has been provided for the clean = Yellow (Cleaning booked in = Date field)
Toolbox talk has been carried out = Green (Tool Box Time & Date = text field)
COVID Restrictions have been lifted = Gray (COVID Restrictions now Lifted = tick box)
Please let me know if you need more information, thanks in advance.
Regards
Cathy
Answers
-
I think you might be better off using automation rules with Change a Cell actions.
Rule 1:
Trigger: When a row is changed, when [Cleaning Booked in] changes to any value
Action: Change Status to "Yellow"
Rule 2:
Trigger: When a row changes, when Tool Box Time & Date changes to any value
Action: Change Status to "Green"
Rule 3:
Trigger: When a row changes, when COVID Restrictions Now Lifted changes to Checked
Action: Change Status to "Gray"
Regards,
Jeff Reisman, IT Business Analyst & Project Coordinator, Mitsubishi Electric Trane US
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
Help Article Resources
Categories
Check out the Formula Handbook template!