Welcome to the Smartsheet Forum Archives
The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.
Conditional Formatting a section
I have a balanced scoresheet. I'd like to have the header "Business Development" Status circle turn from red to yellow to green as the tasks below it are completed. For each task, I set the measurement as "completed" and it turns the Status cell green. Once 1/3 of the status cells are green, I'd like to change the header status circle to YELLOW. Once 2/3 are green, I'd like to turn it to GREEN.
Thoughts?
Comments
-
I built a formula that should work for you!
This formula assumes your header status is in a parent row and all your task status' are child rows.
=IF(COUNTIF(CHILDREN(), "Green") >= ((COUNT(CHILDREN())) * ((2 / 3) - 0.01)), "Green", IF(COUNTIF(CHILDREN(), "Green") >= (COUNT(CHILDREN())) * (1 / 3), "Yellow"))
This will show yellow if >= 1/3 of the children are green, and will show green if >= 2/3 of the children are green.
As for the children, use this formula to turn them green when you mark a task as complete:
=IF([Text2]8 = "Completed", "Green")
