Formula to join three different options to provide a status

Options

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

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    Options

    @Cathy Fraser

    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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!