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
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
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.8K Get Help
- 434 Global Discussions
- 138 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 65 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!