Grandparent status rollup based on parent status - reference parent row
I'm looking to rollup to the grandparent status cell (1) the parent status based on the status of the children. For example:
If Discovery is complete and Gather Requirements children are in progress, roll up "gathering requirements" to grandparent. When that phase is complete and Delivery Planning, Build, Comms has anything in progress, Delivery Planning, Build, Comms gets rolled up to the grandparent.
I am trying to use this PARENT reference in the status column for parent rows 2,6,12 thinking this might be step 1? But this obviously will not work.
=IF(AND(COUNTIF(CHILDREN(), "Complete") = COUNT(CHILDREN()), COUNT(CHILDREN()) <> 0), "Complete", IF(COUNTIF(CHILDREN(), "Not Started") = COUNT(CHILDREN()), "Not Started", =PARENT([Task Name]@row))),
Is this possible without helper columns?
Help Article Resources
Check out the Formula Handbook template!