Changing Status and Health Based on Percentage

MaddyDesautels

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

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"))

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.