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
Best Answers
-
=JOIN(DISTINCT(DESCENDANTS()), ", ") works better but can still cause duplicate names if you enable adding multiple people in a cell
-
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. 👍️
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
Answers
-
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")), ", ")
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
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.
-
=JOIN(DISTINCT(DESCENDANTS()), ", ") works better but can still cause duplicate names if you enable adding multiple people in a cell
-
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. 👍️
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.9K Get Help
- 378 Global Discussions
- 208 Industry Talk
- 441 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 291 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!