Automating Parent Row based off BRYG Children Rows

Josh Patterson

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.

 

 

Thanks,

 

Josh

 

 

Shaun Coulls

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?

 

Cheers,

Shaun

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

 

Craig

svandagriff97421

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