Update Parent Row based on Child Rows

kcaudill1989

I am trying to update Parent Status based on the Status of child rows as stated in the below

If any child rows have a status of In Progress, set parent row status value to "In Progress" If ALL child rows have a status of Complete, set parent row status to “Complete”If the number of child rows with a status of Not Started plus the number of child rows with a blank status equals the number of child rows, set this value to "Not Started"If the number of child rows is equal to 0, leave this value blank

=IF(COUNTIF(CHILDREN(Status@row), "In Progress") > 0, "In Progress", IF(COUNTIF(CHILDREN(Status@row), "Complete") = COUNT(CHILDREN()), "Complete", IF(OR(COUNTIF(CHILDREN(Status@row), "Not Started") = COUNT(CHILDREN()), COUNTIF(CHILDREN(Status@row), "Not Started") + COUNTIF(CHILDREN(Status@row), "") = COUNT(CHILDREN())), "Not Started", IF(COUNT(CHILDREN(Status@row)) = 0, ""))))

The "In Progress" step seems to be working; however, if child status' are all Not Started then it makes the parent record as Complete and i f child status' are all Complete it makes the parent record Not Started.

Can anyone tell me what I have done wrong?

Leibel S

@kcaudill1989

The overall issue you are dealing with is that COUNT() function alone will only bring in non blank cells.

If you want to count also blank cells then you would use something like this:

COUNTIF(CHILDREN(), OR(@cell <> "", @cell = ""))

Let me know if that clears things up...