Symbol Formula including 2 Columns?

Hi All!

I'm working on a Smartsheet and I need a logic formula to update the symbol color. I have 2 columns that I am using in the formula [2 Signer - Status] [1 Signer - Status].

When the status in either of these cells is "Sent" "Delivered" or "Draft", I would like the color of the symbol to be "Yellow".

When the status is "Declined" or "Voided = Red

When the status is "Completed" = Green.

Below is the formula I'm currently using, but it keeps returning #UNPARSEABLE.

I'm not sure which part I'm messing up.

=if([2 Signer - Status]@row [1 Signer - Status]@row = "Sent" "Delivered" "Draft", "Yellow", If([2 Signer - Status]@row [1 Signer - Status]@row = "Declined" "Voided", "Red", If([2 Signer - Status]@row [1 Signer - Status]@row = "Completed", "Green", "")))

Best Answer

  • Kimberly Loveless
    Kimberly Loveless ✭✭✭✭✭✭
    Answer ✓

    Hi @nwilliamsmovement you need to break down the parts and add other formula piece to make it work. I think this should work....

    =IF(OR([2 Signer - Status]@row="Sent",[2 Signer - Status]@row="Delivered",[2 Signer - Status]@row="Draft,[1 Signer - Status]@row = "Sent",[1 Signer - Status]@row ="Delivered",[1 Signer - Status]@row ="Draft"),"Yellow",IF(OR([2 Signer - Status]@row="Declined",[2 Signer - Status]@row="Voided",[1 Signer - Status]@row ="Declined",[1 Signer - Status]@row ="Voided"),"Red",IF(OR([2 Signer - Status]@row="Completed",[1 Signer - Status]@row="Completed"),"Green","")))

Answers

  • Kimberly Loveless
    Kimberly Loveless ✭✭✭✭✭✭
    Answer ✓

    Hi @nwilliamsmovement you need to break down the parts and add other formula piece to make it work. I think this should work....

    =IF(OR([2 Signer - Status]@row="Sent",[2 Signer - Status]@row="Delivered",[2 Signer - Status]@row="Draft,[1 Signer - Status]@row = "Sent",[1 Signer - Status]@row ="Delivered",[1 Signer - Status]@row ="Draft"),"Yellow",IF(OR([2 Signer - Status]@row="Declined",[2 Signer - Status]@row="Voided",[1 Signer - Status]@row ="Declined",[1 Signer - Status]@row ="Voided"),"Red",IF(OR([2 Signer - Status]@row="Completed",[1 Signer - Status]@row="Completed"),"Green","")))

  • Kimberly Loveless
    Kimberly Loveless ✭✭✭✭✭✭

    **Of note with this if one of the columns has a status that would be yellow and one that would be green the symbol would be yellow because that clause comes first in the formula. If you wanted one color to take precedence over the other you would just need to rearrange the formula. **

  • Thanks Kimberly! This formula worked properly and I was able to get it to do what I needed. 😊

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!