Formula to List all SubTask Owners in One Cell
I am hoping to have a column/cell that indicates all team members that contribute to a given project (ideally in a contact list column type). Is there a formula that could reference only the child rows and compile certain child row cell values into a comprehensive list?
For example: For Project A (shown below), Bob is the project owner and there are other team members who own individual tasks. Is there a formula that I could put in the "Contributors" cell on the project row (Row 1) that would automatically list the values in the "Task Owner" cells of all child rows?
Best Answer

You can use a JOIN/DISTINCT combo to generate the string, but it will not be usable as a set of contacts.
=JOIN(DISTINCT(CHILDREN()), "delimiter of choice")
Answers

You can use a JOIN/DISTINCT combo to generate the string, but it will not be usable as a set of contacts.
=JOIN(DISTINCT(CHILDREN()), "delimiter of choice")

Thanks, Paul! Is there a way to exclude duplicate names using the Join formula? For my example above, Bob and Susan would show up twice.

That is what the DISTINCT function above is for.

Perfect  thank you so much!

Happy to help. 👍️

I just tested this out and am getting an "Invalid Data Type" error. Is this due to the column I'm referencing being a Contact List?

Where exactly are you putting the formula? In the same column as the child data or in a different column as in your screenshot?

The formula is in a different column (Contributors Column Row 1 in example above) so I am using the following: =JOIN(CHILDREN([Task Owner]@row), " , "))

The formula you posted should actually be throwing the #UNPARSEABLE error. Are you able to provide a snippet of the formula actually in the sheet similar to the image below?

I accidentally had an extra symbol in there so it's working now with the original formula you provided! Thank you!

Excellent. Happy to help. 👍️
Help Article Resources
Categories
Check out the Formula Handbook template!