Trouble with Formula for RYG/Health/Status Columns

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!

Find more posts tagged with

Formulas

Support

Solution Center

functionality

Conditional Formatting

Project Management

Accepted answers

All comments

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.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.