Parent/Child Formula

Sean Henneous

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:

1. If any child cell below it is blank, skip it
2. If there is a name in any child cell, add it to the parent cell
3. 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

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. 👍️

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. 👍️