Automated Status Ball color change
I have the following formula shown below that changes the Status symbol (Gray/Green/Yellow/Red) based on the Phase/Risk/Change Implementation Date. I tried to update it for a new Phase "Canceled" but I keep getting the error #UNPARSEABLE.
Is this due to the fact that I'm trying to have both the Phase "Operationalize" & "Canceled" show as the Grey status ball?
Formula - Without Canceled (this one works)
=IF([At Risk]1, "Red", IF(Phase1 = "Operationalize", "Gray", IF(Phase1 = "Paused", "Yellow", IF(TODAY() - [Change Implementation Date]1 > 0, "Red", IF(TODAY() - [Change Implementation Date]1 > -7, "Yellow", "Green")))))
Formula - With Canceled Phase (this one doesn't work)
=IF([At Risk]1, "Red", IF(Phase1 = "Operationalize", "Gray", IF (Phase1 = “Canceled”, “Gray”, IF(Phase1 = "Paused", "Yellow", IF(TODAY() - [Change Implementation Date]1 > 0, "Red", IF(TODAY() - [Change Implementation Date]1 > -7, "Yellow", "Green")))))
Comments
-
I think there are a few things happening here, including a missing last parenthesis and a space after IF. Also, I don't think Smartsheet likes the "smart quotes" around Canceled and Gray. Try this:
=IF([At Risk]1, "Red", IF(Phase1 = "Operationalize", "Gray", IF(Phase1 = "Canceled", "Gray", IF(Phase1 = "Paused", "Yellow", IF(TODAY() - [Change Implementation Date]1 > 0, "Red", IF(TODAY() - [Change Implementation Date]1 > -7, "Yellow", "Green"))))))
-
Actually, I think the parentheses are correct.
-
I think Brian nailed it on the head. That space, and smartquotes will kill your formulas everytime.
-
Thank you Brian, that worked!
Question, I notice the visual difference between your quotes around Canceled and Gray but how did you do that?
I press ( Shift + " ) to produce the quotes, is that incorrect?
Thank you again.
-
The smart quotes are usually a result of copying a formula from a different program, like MS Word, and pasting into Smartsheet. If you use a plain text editor like Notepad or type the quotes directly in Smartsheet, you should be okay.
