Using "Flag" in and IF function

Raluca S.

Hello!

I am looking to create a formula for the RYG column that would work like this:

If Status column is "Overdue", then it shows "Yellow"

If Status column is "Complete", then it shows "Green"

If Status column is "In Progress", then it shows "Green"

If Status column is "Not Started", then it shows "Grey"

If At Risk column is flagged, then it shows "Red"

I have been trying this, but the At Risk flag doesn't trigger Red.

=IF(Status@row = "Overdue", "Yellow", IF(Status@row = "Complete", "Green", IF(Status@row = "In Progress", "Green", IF(Status@row = "Not Started", "Gray", IF([At Risk]@row = 1, "Red")))))

Any ideas?

Find more posts tagged with

Formulas

Accepted answers

Danielle Arteaga

I think the issue is the order of your conditions. The formula is parsed in the order you write it, so as soon as a condition is "true," Smartsheet stops evaluating conditions. That means, for example, that as soon as Smartsheet sees a status is "Overdue," it puts the yellow ball in your symbol column and stops evaluating the rest of the conditions, including the "At Risk" flag.

Try this (using the right titles for your columns):

=IF([At Risk]@row = 1, "Red", IF(Status@row = "Overdue", "Yellow", IF(OR(Status@row = "Complete", Status@row = "In Progress"), "Green", IF(Status@row = "Not Started", "Gray", "Gray"))))

Screenshot 2022-11-04 at 11.06.44 AM.png

All comments

Danielle Arteaga

Try this (using the right titles for your columns):

=IF([At Risk]@row = 1, "Red", IF(Status@row = "Overdue", "Yellow", IF(OR(Status@row = "Complete", Status@row = "In Progress"), "Green", IF(Status@row = "Not Started", "Gray", "Gray"))))

Raluca S.

Amazing! Thank you, it worked!! I will remember this.

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.