Parent/Child Formula

I need to create a formula where the parent row determines what it displays in its cell based on the following criteria from the children:

If any child cell below it is blank, skip it
If there is a name in any child cell, add it to the parent cell
If it is a duplicate name in any child cell, skip and do not add to the parent cell

So:

Assigned Tech Column:

Row 1: Blank (literally)

Row 2: Ted

Row 3: Sue

Row 4: Ted

Row 5: Jan

Parent cell displays: Ted, Sue, Jan

I've tried multiple formulas but I just can't seem to get there. Any help would be appreciated.

For clarity the parent row is a roll up of multiple child tasks. Some tasks have not been assigned yet and other task have been assigned. I need to determine each individual tech that is working on this particular project but don't want duplicate techs displayed in the parent cell.

Thanks

Find more posts tagged with

Formulas

functionality

Accepted answers

Sean Henneous

=JOIN(DISTINCT(DESCENDANTS()), ", ") works better but can still cause duplicate names if you enable adding multiple people in a cell

Paul Newcome

That was my fault on the error. My fingers weren't keeping up with the thought flow. Forgot to add in the second CHILDREN function.

=JOIN(DISTINCT(COLLECT(CHILDREN(), CHILDREN(), NOT(ISBLANK(@cell)))), ", ")

Glad you were able to find a working solution. 👍️

All comments

Paul Newcome

When you say "Blank (literally)", do you mean that the cell is blank or that the text "Blank" is entered?

Cell is blank:

=JOIN(DISTINCT(COLLECT(CHILDREN(), NOT(ISBLANK(@cell)))), ", ")

Text of "Blank" is entered:

=JOIN(DISTINCT(COLLECT(CHILDREN(), @cell <> "Blank")), ", ")

Sean Henneous

Thanks for the help. I meant the cell is blank. Currently when I run this I get a #INCORRECT ARGUMENT SET. The only other info that might be important is that this column is a contacts column. Once I saw your response, I was sure it would work and I was hoping to make a slight adjustment: =JOIN(DISTINCT(COLLECT(DESCENDANTS(), NOT(ISBLANK(@cell)))), ", "). Unfortunately neither one is working. I'll continue to plug away and see what I can come up with. Thanks again.

Sean Henneous

=JOIN(DISTINCT(DESCENDANTS()), ", ") works better but can still cause duplicate names if you enable adding multiple people in a cell

Paul Newcome

That was my fault on the error. My fingers weren't keeping up with the thought flow. Forgot to add in the second CHILDREN function.

=JOIN(DISTINCT(COLLECT(CHILDREN(), CHILDREN(), NOT(ISBLANK(@cell)))), ", ")

Glad you were able to find a working solution. 👍️

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.