Once all children are filled with text, parent pulls that text into cell
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
Best Answer
-
Try this instead then:
=IF(COUNTIFS(CHILDREN(), @cell = "") = 0, JOIN(DISTINCT(DESCENDANTS())))
Answers
-
You would need to use the DISTINCT function.
=JOIN(DISTINCT(DESCENDANTS()))
-
@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
-
Try this instead then:
=IF(COUNTIFS(CHILDREN(), @cell = "") = 0, JOIN(DISTINCT(DESCENDANTS())))
-
@Paul Newcome That works, thank you!
-
Happy to help. 👍️
-
@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?
-
That same formula should work on every level.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 471 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!