Formula to List all Sub-Task Owners in One Cell

hbeckner

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?

Paul Newcome

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")

Paul Newcome

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")

hbeckner

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.

Paul Newcome

That is what the DISTINCT function above is for.

hbeckner

Perfect - thank you so much!

Paul Newcome

Happy to help. 👍️

hbeckner

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?

Paul Newcome

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

hbeckner

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), " , "))

Paul Newcome

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?

hbeckner

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

Paul Newcome

Excellent. Happy to help. 👍️