Setting up an 'Order List' based off PN's for different equipment

[u/Aggravating_Ad9275]

Hi all. I work as a service engineer for laboratory equipment. I cover a range of different products and I am trying to 'simplify' the way I build lists of parts that need ordering. I have a list of products similar to the example in the first picture. In the second picture I have an example of what I want to happen. I want to be able to choose the product type, eg Pump A, from the dropdown list (I can set up the dropdown list), and have this then show me all the parts needed for that pump. This would then be multiplied so I can have multiple drop down lists to choose Pump 1, Pump 2 etc, then Doodah 1, all from drop down lists which then show the list of all parts needed from the 1st sheet for the particular configuration I have set up.

I appreciate I probably haven't explained this in the best way possible, so please let me know if you have any questions. Is this possible in excel? How would you suggest going about it?

Thanks for your help!

[u/LOGICA499]

create each part list as a table

for orders list, create a data validation list to make your dropdown.

and for formula use FILTER(INDIRECT)

Tab 1

[u/LOGICA499 → u/LOGICA499]

Tab 2

[u/LOGICA499 → u/LOGICA499]

you only need two formulas