Why are my symbols all red instead of red, yellow and green?

I am using a formula to change the color of my symbol based on whether another column has certain text.

  • If the Status Option column contains the text "OK:" it should trigger the status summary column to show a Green symbol.
  • If the Status Option column contains the text "Needs Attention:" it should trigger the status summary column to show a Red symbol.
  • If the Status Option column contains the text "In Progress:" it should trigger the status summary column to show a Yellow symbol.

=IF(CONTAINS("Needs Attention:", [Status Option]:[Status Option]), "Red", IF(CONTAINS("OK:", [Status Option]:[Status Option]), "Green", IF(CONTAINS("In Progress:", [Status Option]:[Status Option]), "Yellow")))

What is wrong with my formula?


Best Answer

  • Nick Korna
    Nick Korna ✭✭✭✭✭✭
    Answer ✓

    Hi, @Erica Lozano

    Your formulas are checking the whole column, if you amend instances of [Status Option]:[Status Option] to [Status Option]@row, it will check the row and not the whole column. This switch should make things work in sequence through your nested IF statement, though if you have multiple selection allowed in the dropdown you may need to make some further adjustments.

    Hope this helps!

Answers

  • DKazatsky2
    DKazatsky2 ✭✭✭✭✭

    Hi @Erica Lozano ,

    I'm not sure if this is your issue but, since you are allowing multiple values in the drop drown, if there are more than 1 matches, the formula will resolve to the first, which is "Red" in your case.

    For example, if you choose "OK: Sold" and also "Needs Attention:...", it will resolve to "Red"

    Hope this helps,

    Dave

  • Nick Korna
    Nick Korna ✭✭✭✭✭✭
    Answer ✓

    Hi, @Erica Lozano

    Your formulas are checking the whole column, if you amend instances of [Status Option]:[Status Option] to [Status Option]@row, it will check the row and not the whole column. This switch should make things work in sequence through your nested IF statement, though if you have multiple selection allowed in the dropdown you may need to make some further adjustments.

    Hope this helps!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!