Reference EACH child from the Parent on Another sheet
I want to display (from another sheet) the child rows. How can I use a formula, on each of my child lines that displays child1, child2, child3, child4, and child5 in my Release Name & Release column. I only require the ability to do this in these 2 columns. The black text is raw data (and the blue text are formulas).
Sherry Fox
Data Science & Reporting Specialist | Information Technology
United HealthCare Services (UHS)
EAP | Mobilizer | Automagician | Superstar | Community Champion
https://www.linkedin.com/in/sherryfox/
Answers
-
Hey @Sherry Fox
Can I assume that the image above is your Source sheet?
If so, you could use an INDEX(COLLECT formula, referencing the "Release" column as the unique identifier, to bring back row data. It would look something like this:
=INDEX(COLLECT({Release Name Column}, {Release Column}, 202250), 1)
That would bring back the first match, or in this case your parent row. To get the first Child row, you'd swap out the 1 at the end to be 2:
=INDEX(COLLECT({Release Name Column}, {Release Column}, 202250), 2)
And so on:
=INDEX(COLLECT({Release Name Column}, {Release Column}, 202250), 3)
What you could do is have your destination sheet set up so that the Release number and the Child numbers are in two columns, then you can reference these instead of typing them into the formula:
=INDEX(COLLECT({Release Name Column}, {Release Column}, [Release Number]@row), [Child Number]@row)
Let me know if that makes sense!
Cheers,
Genevieve
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now -
Now that makes perfect sense @Genevieve P. ! Thanks so much for taking the extra time to explain the formula based on my screenshot. That really helps so much. I just could not get it before! LOL
Sherry Fox
Data Science & Reporting Specialist | Information Technology
United HealthCare Services (UHS)
EAP | Mobilizer | Automagician | Superstar | Community Champion
https://www.linkedin.com/in/sherryfox/
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.9K Get Help
- 379 Global Discussions
- 210 Industry Talk
- 441 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 300 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!