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")))))

Find more posts tagged with

Sheets

Healthcare

Project Management

Comments

Brian W

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"))))))

Brian W

Actually, I think the parentheses are correct.

Mike Wilday

I think Brian nailed it on the head. That space, and smartquotes will kill your formulas everytime.

Jeffrey H

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.

Brian W

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.

Sign in to join the conversation!

Quick Links

Trending discussions

Document filtered by form responses
I've created a Smartsheets form. When someone submits the form, I want them to automatically be emailed a document that has resources and links, but the document is filtered to only show the content that corresponds with what they selected in the form. Is something like this possible? What types of documents or automations…
Form stopped working after upgrade
I have a sheet that I created in a previous version, and it had a form. I was recently upgraded to a newer version and now my form is not working. I created a new form for the sheet, and it is giving me an error also. Does anyone know what I can do to fix this? The error says Form Unavailable. Sorry, the form you're trying…
Getting Error About Emulated Environments
Hi, I have read the post from 8/21/25. I am still getting the same error. I am setting up a new device and just download the latest version as of 9/5/25. I am using a Eunivon Smart White Board for touchscreen capability. It is running Android 14. Any help would be appreciated.