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.

Automating Parent Row based off BRYG Children Rows


I am looking for a formula that will mark a parent row "Green" when and only when all children rows have been marked "Green" for completion. Each children row has a formula that bases of when each task was completed by date and will mark that row "Green" for completion dates that are today or in the past.










  • Shaun Coulls
    edited 09/25/16

    You could create a new column that uses an IF statement to display a 1 if the symbol  row is anything other than green.


    On the parent for the new column make the formula =sum(children())


    On your parent row use an IF statement for the symbol, IF this value is > 0 then at least one of the children is not green.


    Not sure if you could change the symbol column to a numeric value 1=green, 2=yellow ect and then you would be able to do the math on the symbol column rather than creating a new column.


    Hope that helps?




  • J. Craig Williams
    J. Craig Williams ✭✭✭✭✭✭

    Without adding a column, you could add this formula to the parent row:


    =IF(COUNT(CHILDREN()) = COUNTIF(CHILDREN(), "Green"), "Green", "Something else")


    Replace "something else" with either a color or blank as desired.


    Hope this helps



  • svandagriff97421
    edited 07/25/19

    I need a parent column to show overall (automated average) Health of the children rows of RYG in that column, as the date tells the cell its red then the overall should show red: (In this IF statement row has the due date cell telling the Health status cell to automate as R, Y, or G depending on logic of set days before or after)

    =IF(COMPLETE84 <> 1, IF(TODAY() - [DUE DATE]84 > 0, "Red", IF(TODAY() - [DUE DATE]84 > -3, "Yellow", "Green")), "Green")

    This IF STATEMENT TELL THE OVERALL CELL TO AUTOMATE/ROLL-UP THE ROW RIGHT BELOW (I now need all 11 rows below to automate individually, meaning if row 2 is red the overall health should be red, if row 2,3,4, or more are R, Y and/or G the overall health should be yellow. If all are green the overall should be green. If all are red the overall should be red?

    =IF(COMPLETE83, 6 <> 1, IF(TODAY() - [DUE DATE]83 > 0, "Red", IF(TODAY() - [DUE DATE]84 > -3, "Yellow", "Green")))



This discussion has been closed.