BOM - INDEX/COLLECT Formula referencing child rows only in another sheet

Andrew Watts

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!!

Andrew Watts

@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

Paul Newcome

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

Andrew Watts

Thanks!

Paul Newcome

Happy to help. 👍️