How can I get the status of a parent row to update when all child rows are blank?

kwilson

I'm trying to write a formula for:

If all child statuses are Passed then parent status is Closed

If all child statuses are Passed or Retest Passed then parent status is Closed

If all child statuses are Not Started then parent status is Not Started

If child statuses are blank or Not Started then parent status is Not Started

If all child statuses are blank then parent status is Not Started

This is not working

=IF(COUNTIFS(CHILDREN(), "Passed") = COUNT(CHILDREN()), "Closed", IF(COUNTIFS(CHILDREN(), OR(@cell = "Passed", @cell = "Retest Passed")) = COUNT(CHILDREN()), "Closed", IF(COUNTIFS(CHILDREN(), OR(@cell = "Not Started", @cell = “ “)) = COUNT(CHILDREN()), "Not Started")))

Heather_Verde

So first question is do you only have child rows or do you have grandchildren and more descendants. If you only have children, you can replace DESCENDANTS below with CHILDREN. I just did a similar exercise, but I was only changing the status if completed. I tried out the below and it worked for me (you may need to remove one of the end parenthesis). The first =IF(COUNT(DESCENDANTS()) > 0 determines if it is a descendant/child. For your "Passed" and "Retest Passed", you only need one OR statement IF(COUNT(DESCENDANTS()) = COUNTIFS((DESCENDANTS()), OR(@cell = "Passed", @cell = "Retest Passed")), "Closed", so in yours the first "Passed" is not necessary. The last part updates to "Not Started" if it's Not Started or blank IF(COUNT(DESCENDANTS()) = COUNTIFS((DESCENDANTS()), OR(@cell = "Not Started", @cell = " ")), "Not Started"))). If you want a default to appear, you can add it after the last "Not Started". IE: IF(COUNT(DESCENDANTS()) = COUNTIFS((DESCENDANTS()), OR(@cell = "Not Started", @cell = " ")), "Not Started", "Pending")))

Full formula:

=IF(COUNT(DESCENDANTS()) > 0, IF(COUNT(DESCENDANTS()) = COUNTIFS((DESCENDANTS()), OR(@cell = "Passed", @cell = "Retest Passed")), "Closed", IF(COUNT(DESCENDANTS()) = COUNTIFS((DESCENDANTS()), OR(@cell = "Not Started", @cell = " ")), "Not Started")))

kwilson

The formula worked but it's not quite right. It's changing it to closed when only one of the child rows is Passed or Retest Passed. They ALL need to be Passed or they ALL need to be Passed or Retest Passed in order to mark the parent closed. It's also not defaulting to Not Started if they are all blank.

=IF(COUNT(CHILDREN()) > 0, IF(COUNT(CHILDREN()) = COUNTIFS((CHILDREN()), OR(@cell = "Passed", @cell = "Retest Passed")), "Closed", IF(COUNT(CHILDREN()) = COUNTIFS((CHILDREN()), OR(@cell = "Not Started", @cell = " ")), "Not Started")))

Heather_Verde

Since it's the blank cells that are causing an issue, you may have to create a helper column that looks at the status to see if it's blank and if so populates the helper column. Then you would also need to reference the helper column in your formula.