Parent/Child Formula

Options

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

Best Answers

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    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
    Options

    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
    edited 01/29/20 Answer ✓
    Options

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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!