Changing Status and Health Based on Percentage

Hi guys!

I'm fairly new to Smartsheets (and project management) and i'm the first and only project manager at my company. I was looking for help with conditional formatting and and IF AND function. Any and all help would be greatly appreciated!

What I'm looking to do is (seemingly) basic:

If my "Percentage Completed" row is = 100% then my status row should be "Complete" and my health row should be "Green"

If my "Percentage Completed" row is less than 100% then my status row should be "In Progress" and my health row should be "Yellow"

And lastly, If my "Percentage Completed" row is 0% then my status row should be "Not Started" and my health row should be "Red"

The formula I have for changing status (based on percentage complete) is below, however the "in progress" is not working. I still haven't found a formula or Conditional Formatting for the health row.

=IF([Percent Completed]@row=100,"Complete",IF([Percent Completed]@row=0,"Not Started",IF(AND([Percent Completed]@row > 0, [Percent Completed]@row < 100, "In Progress"))))

I've also attached a photo so that you can get a better idea of what I'm looking for!

Screen Shot 2022-07-25 at 12.13.44 PM.png

Thank you in advance for your help!

Madeline

Find more posts tagged with

Formulas

Conditional Formatting

Accepted answers

Paul Newcome

Try these in their respective columns...

Status:

=IF([Percent Complete]@row = 1, "Complete", IF([Percent Complete]@row = 0, "Not Started", "In Progress"))

Percent Complete:

=IF([Percent Complete]@row = 1, "Green", IF([Percent Complete]@row = 0, "Red", "Yellow"))

All comments

Paul Newcome

Try these in their respective columns...

Status:

=IF([Percent Complete]@row = 1, "Complete", IF([Percent Complete]@row = 0, "Not Started", "In Progress"))

Percent Complete:

=IF([Percent Complete]@row = 1, "Green", IF([Percent Complete]@row = 0, "Red", "Yellow"))

MaddyDesautels

https://community.smartsheet.com/discussion/comment/337349#Comment_337349

Tried both of these but unfortunately they're coming up as "unparsable" :(

Paul Newcome

My apologies. I had some typos. Change every instance of [Percent Complete] to [Percent Completed] so that the columns referenced match the columns in the sheet.

J Bauer

I am trying to do the opposite but it's not working. =IF([Status]@row = "Complete", 100, IF([Status]@row = "Not Started", 0, 50)).

Paul Newcome

@J Bauer Can you provide more context as to why it is "not working"? Are you getting an error message or an unexpected output?

J Bauer

@Paul Newcome - It is just displaying the formula. When doing it the other way it works. Maybe a dependency on the sheet???

Paul Newcome

@J Bauer You cannot use formulas in columns being populated via the dependency settings.

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, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.