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!

Help Article Resources
Categories
Check out the Formula Handbook template!