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

Find more posts tagged with

Formulas

Accepted answers

All comments

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. 👍️

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.