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
- Smartsheet Customer Resources
- 63.1K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 444 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 450 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 290 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!