Formula

Kim Michael

I'm looking for a formula to put in a top line column cell of a sheet that will collect all of the resources listed in the same sheet in an "assign to" column. The "assign column" is a column list and has named for of various resource. The resources vary by task row.

Goal is to have a list at the top line to show all resources involved in the project. This cell will be linked to another sheet.

How can I best do this?

hollyconradsmith

Hi @Kim Michael,

Use indent to make the top row a parent. https://help.smartsheet.com/articles/504734-hierarchy-indenting-outdenting-rows

Then use =Join(Distinct(Children([assigned to]@row)), ", ")

https://help.smartsheet.com/function/distinct

https://help.smartsheet.com/function/children

Kim Michael

The sheet is already set p with parent and child rows for the tasks. I placed your formula into my top row but nothing happened. Here is a section of the schedule. I am trying to get a formula to show (in red boxed area) the all the names listed under "Assigned To" In this example it would be Kim Michael and Sarah Bright.

hollyconradsmith

Hi @Kim Michael There are no assignees in the direct children of that top row. Try pasting the formula into the grey "resources and schedule" row since there are values in the assigned to column children of that section.

Kim Michael

Thank you for your patience. I see that I will need a child collection for each gray area. Not sure what formula I should use to show for the list of all of the resources assigned to to those children tasks. I did try the =Join(Distinct(Children([assigned to]@row)), ", ") but no luck.

I have do it for flag areas with this formula =IF(COUNTIF(CHILDREN(), =1) > 0, 1, 0). Since the columns I am trying to collect is a contact list not sure how to do the children formula to collect all resources listed for each area then the top line listing all resources for all the children. Hope this makes sense.

hollyconradsmith

Hi @Kim Michael

I suspect that if you paste the formula into all the grey rows and then also into the top row, it should work, since the grey rows are children of the top row.

Kim Michael

Unfortunately it did not work. when I put them in the gray rows or top row. Thank you

hollyconradsmith

@Kim Michael ,

Are you using resource management in your project sheet? If you are, you can't use formulas in the "assigned to" column.

Kim Michael

Ahhhh that would be a hard yes. Thanks for getting me there. I really appreciate your time and effort.