Conditional Formatting a section

R.Holden

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? 

Travis

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