At Risk column symbol formatting based on status of another column help

Brandon R

I'm trying to have my At Risk column automatically assign a colored symbol based off selected status in a Status column. This is my current formula I built off other users post:

=IF(Status@row = Not Started, "Red", IF(Status@row = In Progress, "Yellow", IF(Status@row = Complete, "Green")))

I'm getting the #UNPARSEABLE error after hitting enter. I am VERY new to SS and could really use some help. Thanks in advance!

Paul Newcome

You need to put quotes around all text strings in formulas to include "Not Started", "In Progress", and "Complete".

Paul Newcome

Try this instead:

=IF(OR(Status@row = "Complete", Status@row = "Not Applicable"), "Green", IF(OR([End Date]@row< TODAY(), Status@row = "Blocked"), "Red", IF([End Date]@row<= TODAY(2), "Yellow", "Green")))

Paul Newcome

You need to put quotes around all text strings in formulas to include "Not Started", "In Progress", and "Complete".

Brandon R

Well darn...that was a very simple fix to my issue. Thank you so much Paul! I'll do better going forward!

Paul Newcome

Happy to help. 👍️

Brandon R

Here's another one for you. If I wanted to also have it look at the date what would be the proper syntax? In other words, I don't want a red symbol to appear if I'm passed today's date and the status is Complete. The previous advice fixed my broken formula but I didn't account for a status.

Thanks again for your help!

Paul Newcome

I'm not sure I follow. Your previous formula was nothing but Status. What is your updated formula?

Brandon R

This is the new formula I tried and received a #UNPARSABLE error:

=IF([End Date]@row < TODAY() OR [Status] = "Not Started", "Red", IF([End Date]@row = TODAY() OR [Status] = "In Progress", "Yellow", IF([End Date]@row > TODAY() OR [Status] = "Complete", "Green")))

If the Status column shows Complete but that particular task End Date has passed then I don't want the symbol in Risk Level to be red.

Paul Newcome

Looks like your OR syntax is off.

=IF(OR([End Date]@row< TODAY(), Status@row = "Not Started), "Red", .....................

Brandon R

Would I have to list each status out the way you have like this:

+IF(OR([End Date]@row < TODAY(), [Status]@row = "Not Started"), "Red", IF(OR([End Date]@row < TODAY(), [Status]@row = "In Progress"), "Red", IF(OR([End Date]@row < TODAY(), [Status]@row = "Delayed"), "Red")))

Ideally I'm trying to create one formula in the Symbols column for each of these results on my SS:

If a task is not over due then the symbols should show:

• Complete - Green
• In Progress - Green
• Not Started - Green
• Blocked - Red
• Not Applicable - Green

If a task is within 2 days of being due:

• Complete - Green
• In Progress - Yellow
• Not Started - Yellow
• Blocked - Red
• Not Applicable - Green

If a task is over due:

• Complete - Green
• In Progress - Red
• Not Started - Red
• Blocked - Red
• Not Applicable - Green

This formula was giving me some good results but still gave me a #INCORRECT ARGUMENT SET error:

=IF(OR([End Date]@row < TODAY(), Status@row = "Not Started"), "Red", IF(OR([End Date]@row < TODAY(), Status@row = "In Progress"), "Yellow", IF(OR([End Date]@row < TODAY(), Status@row = "Complete"), "Green", IF(Status@row = "Not Applicable"), "Blue", IF(Status@row = "Delayed"), "Red")))

And sorry for the late reply. I have been trying different formula configurations to no avail. Thanks again!

Paul Newcome

Try this instead:

=IF(OR(Status@row = "Complete", Status@row = "Not Applicable"), "Green", IF(OR([End Date]@row< TODAY(), Status@row = "Blocked"), "Red", IF([End Date]@row<= TODAY(2), "Yellow", "Green")))

Brandon R

I really got too complex with my formula didn't I? Thanks so much! That's exactly what I needed for my status column!

Paul Newcome

Happy to help. 👍️