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

edited 12/09/19 in Archived 2015 Posts

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.




  • Travis
    Travis Employee

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

This discussion has been closed.