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!
Thank you in advance for your help!
Madeline
Best Answer
-
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"))
Answers
-
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"))
-
Tried both of these but unfortunately they're coming up as "unparsable" :(
-
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.
-
I am trying to do the opposite but it's not working. =IF([Status]@row = "Complete", 100, IF([Status]@row = "Not Started", 0, 50)).
-
@J Bauer Can you provide more context as to why it is "not working"? Are you getting an error message or an unexpected output?
-
@Paul Newcome - It is just displaying the formula. When doing it the other way it works. Maybe a dependency on the sheet???
-
@J Bauer You cannot use formulas in columns being populated via the dependency settings.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.6K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 63 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!