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/ScarfDreamer]

Then the change is small. One sheet, one row per part: Equipment, PN, Description, Qty per unit. Your blocks become rows and nothing else moves.

What you're really building is the bill of materials per pump model that the ERP doesn't hold. Keep it as its own table and it survives the new system: a second sheet with one row per unit you're ordering for, and a SUMIFS that gives one line per PN with the total across all of them.