Formula
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?
Answers
-
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)), ", ")
Holly Conrad Smith
Director of Technology & Innovation 💡 at Streamline
CliftonStrengths Top 5: Deliberative, Restorative, Achiever, Consistency, Harmony
-
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.
-
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.
Holly Conrad Smith
Director of Technology & Innovation 💡 at Streamline
CliftonStrengths Top 5: Deliberative, Restorative, Achiever, Consistency, Harmony
-
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.
-
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.
Holly Conrad Smith
Director of Technology & Innovation 💡 at Streamline
CliftonStrengths Top 5: Deliberative, Restorative, Achiever, Consistency, Harmony
-
Unfortunately it did not work. when I put them in the gray rows or top row. Thank you
-
Are you using resource management in your project sheet? If you are, you can't use formulas in the "assigned to" column.
Holly Conrad Smith
Director of Technology & Innovation 💡 at Streamline
CliftonStrengths Top 5: Deliberative, Restorative, Achiever, Consistency, Harmony
-
Ahhhh that would be a hard yes. Thanks for getting me there. I really appreciate your time and effort.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 379 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 304 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!