How to List Parents By Common Child
I am working on a project book of work and currently have a list of resources listed by project:
Project 1:
- Rich
- John
- Angela
- Carly
- Ted
Project 2:
- Tim
- Jeff
- Rich
- Ted
Project 3:
- Sam
- Tim
- Ted
I am looking to create another sheet that will automatically pull from this first sheet and show the resource and the projects that they are in:
Rich
- Project 1
- Project 2
Ted
- Project 1
- Project 2
- Project 3
Angela
- Project 1
How could I automatically pull the parent information where a resource is listed multiple times. Any help will be appreciated.
Thanks!
Answers
-
Hi @Richn
Hope you are fine, Please add a copy of your sheet or a screenshot (after removing or replacing any sensitive information). This will make it easier for me to provide the appropriate answer to your question.
Or if you like me to fix the formula directly on your sheet please share me as an admin on a sample copy of your sheets ( Source & Destination ) and i will Create the exact formula for you then you can copy it to your original sheet.
My Email for sharing : Bassam.k@mobilproject.it
bassam.khalil2009@gmail.com
☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"
-
Hi @Richn
Can I clarify, is your first example above one sheet that has each Project listed as a Parent Row, with the resources indented below it (on child rows)?
If so, we will just need to add in one helper column to this main source sheet. This column will auto-populate the Project Name from the Parent row into each of the Child Rows using a column formula. Try something like this:
=PARENT([Primary Column]@row)
This is assuming your Project title is listed in the Primary column.
Then once you've turned this into a column formula, you can either create a Report and Group this Report by Resource, or you could use a JOIN(COLLECT cross-sheet formula to list every project associated with each resource in one cell.
Let me know if you'd like to see screen capture examples of any of the above information and I'm happy to explain further!
Cheers,
Genevieve
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
Hi Genevieve,
That is correct, the projects are listed in one sheet as parent rows with resources below as child rows.
Screen captures would be great if you could!
-
Hi @Richn
No problem!
Here is the Helper Column showing the formula I used to bring down the Project Name to each individual row. You can hide this column once you've created it:
Then I would create a Report, excluding any Parent Rows by filtering, then and Group by the resources:
I've collapsed some of the top resources as they only have 1 project assigned, but you can see that Ted shows each of his projects listed individually. This is because we pulled the Parent row information down into each child row so it can be displayed in the Report.
Will this work for you?
Cheers,
Genevieve
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
Thanks Genevieve! This worked. One more question- how do I filter out the parent row in the report?
-
Hi @Richn
I'm glad this worked for you!
In my example, you'll see that every Parent row is blank in the Helper Column. I used the filter to say that column "Is not blank", does that make sense?
Cheers,
Genevieve
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.8K Get Help
- 406 Global Discussions
- 218 Industry Talk
- 457 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 297 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!