BOM - INDEX/COLLECT Formula referencing child rows only in another sheet
Hey there,
If anyone can help.
I have set up two calculation sheets. The first is a Bill of Materials with the second being a variations sheet where I can combine various options from the BOM sheet. Each BOM item has a corresponding unique ID number.
In the Variations Sheet I want to have a list of the BO so I can easily reference what parts I have available.
I have tried using the below formula but it only pulls the parent row of the BOM list. I need it to instead pull the ID Numbers from the CHILD rows only.
=INDEX(COLLECT({TEST - Pricing - Parts Range 2}, {TEST - Pricing - Parts Range 3}, [Parts BOM]@row), 1)
I am trying to put the formula in the second image, ROWID@row for reference.
Thanks in advance!!
Answers
-
@Paul Newcome I have seen in other threads you seem to be the guru on INDEX formulas. Any chance you can lend a hand on this one?
Thanks
-
You would need a helper column on the source sheet that brings the data from the parent row onto each child row. From there you would reference the helper column in your INDEX/COLLECT
-
Thanks!
-
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 380 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
- 305 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!