Once all children are filled with text, parent pulls that text into cell

aNewTPManager

Working on a cert tracker. It requires the trainee to sign off on tasks then that task parent will auto complete.

I want to check that, once all the children "trainee initials" are filled, it pull that same text into the parent.

I found that =JOIN((DESCENDANTS())) works but it does it for any child that filled. I would like it for ,once all descendants are filled in, to populate. I feel that there should be an AND function but I can't figure out the syntax

Paul Newcome

Try this instead then:

=IF(COUNTIFS(CHILDREN(), @cell = "") = 0, JOIN(DISTINCT(DESCENDANTS())))

Paul Newcome

You would need to use the DISTINCT function.

=JOIN(DISTINCT(DESCENDANTS()))

aNewTPManager

@Paul Newcome Thanks for answering. I'm not sure I was clear on what I'm looking for.

With either formula [=JOIN(DISTINCT(DESCENDANTS())) or =JOIN((DESCENDANTS()))], the parent populates when any child in filled. I would like it to populate only after all children are filled since it's going to be the same text in each child.

Right now, I get this which is what I don't want

Once all task child are filled, I would the task parent to be "signed off" as well

Thanks for helping in any case

Paul Newcome

Try this instead then:

=IF(COUNTIFS(CHILDREN(), @cell = "") = 0, JOIN(DISTINCT(DESCENDANTS())))

aNewTPManager

@Paul Newcome That works, thank you!

Paul Newcome

Happy to help. 👍️

aNewTPManager

@Paul Newcome One more question. How can I do same thing for grandparents and on?

My list breaks down like starting with a parent then each line being a child of the previous

level

section (safety, equipment,readings, etc)

tasks

In other words, once all tasks have initials, then the parent adds those same initials. Once all sections are signed, the next parent get initials, and so on.

Is there a general formula that can handle this?

Paul Newcome

That same formula should work on every level.