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



Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!