Can health status be dependent on another cell's value?

I would like to make a column of health dependent on the status column. Not started -> grey, at risk -> red, etc. I typed the following formula but am not able to generate results. Is this a possibility in SS? If so, what am I doing wrong? I'd like to apply the formula to a whole column, but SS is kicking back a syntax error when I convert to column formula. Screenshot attached as well.

=IF(Status3 = "Not Started", "grey" = IF(Status3 = "In Progress", "yellow", =IF(Status3 = "At Risk", "red", =IF(Status3 = "completed"))))


  • JamesB
    JamesB ✭✭✭✭✭


    When using nested formulas, only your first formula starts with an = sign. For the others remove the equal and replace with a comma.

    =IF(Status3 = "Not Started", "grey" ,IF(Status3 = "In Progress", "yellow", IF(Status3 = "At Risk", "red", IF(Status3 = "completed","Green"))))

  • That formula works for a single cell but the syntax error is still produced when the column formula conversion is executed. Also, I may not have been clear. I have the "Health" column as a symbol type, not a text type. So I am hoping for the color written in the formula to convert to a symbol, since the list type is such. That formula only output the color written in text, which seems like an obvious moment for me now....

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    You cannot have a "hardcoded" row number in a column formula. Try changing each instance of Status3 to Status@row.

    To get it to output the color, you need to make sure you are using the proper text string in the formula (case sensitive). To check what you need (this is true for any symbol column), click on the small arrow to the right of any cell as if you are going to manually select one of the colors from the dropdown. The text you see there will be the text you need to use in your formula.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!