Grandparent status rollup based on parent status - reference parent row

JenS

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?

Genevieve P.

Hi @JenS

I would actually use one Helper Column in this instance. It would identify the Child Rows with "In Progress" and then return the Parent Name:

=IF(COUNT(CHILDREN([Primary Column]@row)) = 0, IF(Status@row = "In Progress", PARENT([Primary Column]@row)))

Note that you can apply this to the entire column as a column formula, but then you'll need to revert it back to a cell formula so you can delete it from the top row cell (so it doesn't error when we add the second formula in).

Then we can use this Column to filter and find all rows with data, bringing that back up to the top cell. To make sure you're not repeating the same parent name, you can use a DISTINCT function:

=JOIN(DISTINCT(COLLECT([Helper Column]:[Helper Column], [Helper Column]:[Helper Column], <>"")), " / ")

Let me know if this works for you!

Cheers,

Genevieve