Displaying a symbol in a column based on the combination of values in two other columns

Options

Hi,

I am trying to figure out how to display a symbol based on the values of the Result column and the Status column as follows:

Is there a way to accomplish this with a formula or do I need to use reference sheets?

Thanks!

Best Answer

  • Carson Penticuff
    Carson Penticuff ✭✭✭✭✭✭
    Answer ✓
    Options

    Well... yes it can be done... but those are quite a few conditions. Try this:

    =IF([Result]@row = "", IF(OR([Status]@row = "", [Status]@row = "Not Started", [Status]@row = "In Progress", [Status]@row = "Complete"), "", IF([Status]@row = "Delayed", "Hold", IF([Status]@row = "Rollback", "No"))), IF([Result]@row = "Fail", IF(OR([Status]@row = "", [Status]@row = "Not Started", [Status]@row = "In Progress", [Status]@row = "Delayed"), "Hold", IF(OR([Status]@row = "Complete", [Status]@row = "Rollback"), "No")), IF([Result]@row = "Success", IF(OR([Status]@row = "", [Status]@row = "Not Started", [Status]@row = "In Progress", [Status]@row = "Delayed"), "Hold", IF([Status]@row = "Complete", "Yes", IF([Status]@row = "Rollback", "No"))))))

Answers

  • Carson Penticuff
    Carson Penticuff ✭✭✭✭✭✭
    Answer ✓
    Options

    Well... yes it can be done... but those are quite a few conditions. Try this:

    =IF([Result]@row = "", IF(OR([Status]@row = "", [Status]@row = "Not Started", [Status]@row = "In Progress", [Status]@row = "Complete"), "", IF([Status]@row = "Delayed", "Hold", IF([Status]@row = "Rollback", "No"))), IF([Result]@row = "Fail", IF(OR([Status]@row = "", [Status]@row = "Not Started", [Status]@row = "In Progress", [Status]@row = "Delayed"), "Hold", IF(OR([Status]@row = "Complete", [Status]@row = "Rollback"), "No")), IF([Result]@row = "Success", IF(OR([Status]@row = "", [Status]@row = "Not Started", [Status]@row = "In Progress", [Status]@row = "Delayed"), "Hold", IF([Status]@row = "Complete", "Yes", IF([Status]@row = "Rollback", "No"))))))

  • Shari D
    Shari D ✭✭
    Options

    It works beautifully! You are a genius! Thank you!

  • Carson Penticuff
    Carson Penticuff ✭✭✭✭✭✭
    Options

    I'm glad I could help!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!