Pull reference data from another sheet while filtering out based on a multi-select dropdown

Matt Sommer

Hello,

I'm new to Smartsheet and I'm having a bit of trouble with referencing data from one sheet to another. I think this question has been asked multiple times, but I'm confused on the format of the formula's as this is a complex problem.

I want to have a master 'Parts List' which is a complete list of parts with some attributes. One of the attributes is the product the part is on. Here is a screenshot.

Using that master table, I want to have another sheet which only shows the parts which are on a specific product. So as example below, listing only parts that are on 'Ford Focus'. I want to be able to pull some of the columns such as 'Number' and 'Name', and 'Category'.

I think the solution is to use INDEX and COLLECTION, but I'm really confused on the format of the formulas. I'm still learning the reference syntax but it's confusing as I'm so new to Smartsheet.

This seems like the closest solution I could find: https://community.smartsheet.com/discussion/75675/vlookup-function-applied-only-to-a-filtered-data-in-another-sheet

Paul Newcome

Have you looked into creating a report?

Matt Sommer

https://community.smartsheet.com/discussion/comment/288686#Comment_288686

Sorry, I forgot to specify, the reason I can’t use a report is I need to use hierarchy in the bill of materials spreadsheet. I need to be able to show parts within parts.

Paul Newcome

Are you able to provide a screenshot with manually entered data that shows the final desired outcome?

Matt Sommer

https://community.smartsheet.com/discussion/comment/288741#Comment_288741

If the data looks like the second screenshot, and the data is in a sheet, it will work.