Trouble with Formula for RYG/Health/Status Columns

Megan Nash

Hi There,

I'm brand new to Smart Sheet and still learning the abundance of inner workings - hoping to find some help with what is probably an easy formula but I can't seem to get it right!

I'm trying to change the color of the RYG ball under my 'Health' column based on the 'Status' column. For instance:

Red = Canceled/Not Started

Yellow = On Hold

Green = In progress

Complete = Blue

=IF([Status]1 = "In Progress"; "Green", IF(OR([Status]1 = "Not Started", [Status]1 = "Canceled"); "Red", IF(OR([Status]1 = "On Hold"; "Yellow")))

Thank you to any and all who can lend a hand in solving this mystery, most appreciated!

Paul Newcome

You are actually pretty close. Try something like this...

=IF(Status@row = "Complete"; "Blue"; IF(Status@row = "In Progress"; "Green"; IF(Status@row = "On Hold"; "Yellow; "Red")))

Megan Nash

Hi Paul,

Thank you so much for your help! I tried implementing this morning and I get an error...#UNPARSEABLE?

Perhaps I am inputting into the wrong cell/column? Hopefully this screen shot helps. Truly appreciate your help!

Paul Newcome

Are you able to post a screenshot of the formula actually in the sheet similar to my screenshot below?

Bhavik Mishra

=IF(Status@row = "Complete"; "Green"; IF(Status@row = "In Progress"; "Yellow"; IF(Status@row = "Needs to be done";"Red")))

I tired please see below:

Bhavik Mishra

Its not working and i am getting error

Paul Newcome

@Bhavik Mishra Do you have a column in your sheet called "Status"?

Bhavik Mishra

Yes sir i have a status column in my sheet.

Paul Newcome

Is it actually called "Status" or is it called something else? Do you normally use the semicolon in between sections of a function or do you normally use a comma?

Bhavik Mishra

Its actually called Status and i tried both semicolon and coma but still getting same error.

Paul Newcome

Try this...

=IF(Status@row = "Complete", "Green", IF(Status@row = "In Progress", "Yellow", IF(Status@row = "Needs to be done", "Red")))

Bhavik Mishra

wow, its worked and thank you so much for your help.

Paul Newcome

Happy to help. 👍️

Looks like it was commas vs semicolons.