Need Formula for Parent Row to Reflect Overall Status Based on Child Row

Alex Matsuo

I'm currently working on a project tracking sheet, and right now I have a column with statuses like, "In Progress", "Completed", "Not Started", "At Risk", etc.

There is a parent row with the project name and status. If any element of the child rows is "In Prgress", I'd like the status cell in the parent row to also say, "In Progress." Once every child row in the Status column is marked "Completed", I'd like the Status cell to also say "Completed."

To add another layer of complexity, I'd also like the format of the parent row to change to bolded red if any of the Statuses are late or marked "At Risk."

I hope that makes sense...

Anson Cheung

Hi Alex,

a) First, you need to have a formula column to derive the parent status according to the children row with sth like

=IF(COUNTIF(CHILDREN([Status]@row), HAS(@cell, "In Progress"))>0, ... , ...)

b) Then, you need another formula column to derive the parent row count. E.g.

=COUNT(ANCESTORS())

It would be zero if the row is a parent row.

c) Next, you need to add a conditional formatting setting to bolded red with two conditions:

the result of (a) is late or at risk AND the count of (b) = 0

--Anson