Red, Yellow, Green, Gray automation formula help needed

Kelly Ospina

I have a sheet with a drop down column, then a symbol column. I am trying to automate the color of the symbol in the symbol column based on the drop down choice selected in the drop down column.

This is the formula I'm using, but I keep getting "unparsable" back:

=IF([Estimated Days to Close]@row = "0 - 90 Days"; "Green"; IF([Estimated Days to Close]@row = "91 - 120 Days"; "Yellow"; IF([Estimated Days to Close]@row = "121 Days or greater"; "Red"))))

NOTE: I haven't added it yet, but the final color will be gray if none of the other conditions have been met.

Any help would be appreciated. Thank you!

Paul Newcome

It is only an extra parenthesis at the end...

=IF([Estimated Days to Close]@row = "0 - 90 Days"; "Green"; IF([Estimated Days to Close]@row = "91 - 120 Days"; "Yellow"; IF([Estimated Days to Close]@row = "121 Days or greater"; "Red")))

Here's a tip (two actually):

1. If all you have are nested IF statements, then you can count your IF's and that should be the number of closing parenthesis on the end.
2. If all that is left is closing parenthesis at the end of the formula, you can actually exit the cell and Smartsheet will automatically add enough closing parenthesis to close everything out.

Adding in Grey:

=IF([Estimated Days to Close]@row = "0 - 90 Days"; "Green"; IF([Estimated Days to Close]@row = "91 - 120 Days"; "Yellow"; IF([Estimated Days to Close]@row = "121 Days or greater"; "Red"; "Grey")))

Kelly Ospina

Hi Paul, I just tried it and I'm still getting unparsable. I'm not sure what I'm doing wrong still?

Here is a published test sheet as an illustration: https://app.smartsheet.com/b/publish?EQBCT=9d9f08f7fd914a00af080ec561d2f913

Paul Newcome

The syntax looks correct. Depending on your region... You may try swapping the semicolons out for commas.

Kelly Ospina

That did it! I wasn't aware there was a regional difference, thank you so much!

Paul Newcome

Happy to help! 👍️

Please don't forget to mark the most appropriate response(s) as being "helpful" so that others with a similar issue know a solution can be found here.