Update parent value if all children are a specific value, leave alone otherwise

jcabaniss

I'd like to update the main parent row cell to "Complete" if all the children are also set to "Complete". Otherwise, I'd like the parent row cell to just be left alone (it will be blank until everything else is "Complete)".

I can make this happen based on the other statuses the children show by using a bunch of COUNTIFS expressions, but I'm trying to see if I can simplify this with something like this: "IF(AllChildren = "Complete", "Complete").

This is an example formula that does what I want but goes through all combinations of the childrens' statuses and is long and clumsy (I think): =IF(Status@row = "Complete", "Blue", IF(COUNTIFS(Health2:Health74, "Red") > 0, "Red", IF(COUNTIFS(Health2:Health74, "Yellow") > 0, "Yellow", IF(COUNTIFS(Health2:Health74, "Green") > 0, "Green"))))

Thank you!

Paul Newcome

Try this:

=IF(COUNTIFS(CHILDREN(), @cell <> "Complete") = 0, "Complete")