How to generate a Picking List of Components that are linked to a Product Code?

[HWall97]

Hi,

I currently have a sheet where order quantities can be entered for specific codes (please see 'Order Table'). These codes are for 'kits' which require several components to be made (please see 'Components Table'). Please see below for an example, the actual spreadsheet has about 23 different codes for 'kits.' The components that are required for each code are specified in the Components table (e.g. Code 21.1350 requires 1 x 123, 6 x 456, 20 x 789).

What I would like to have in the end, is a picking list for the components needed (in reality, for any given 'kit' we have there will be at least 10 components required). I want the picking list to look something like this if possible:

[TABLE="class: outer_border, width: 500"]
<tbody>[TR]
[TD]KIT QTY[/TD]
[TD][/TD]
[TD]CODE[/TD]
[TD]DESCRIPTION[/TD]
[TD]COMPONENT QTY[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]21.1350[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]123[/TD]
[TD]Pipe[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]456[/TD]
[TD]Cable Tie[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]789[/TD]
[TD]100mm Screw[/TD]
[TD]20[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]21.1550[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]123[/TD]
[TD]Pipe[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]456[/TD]
[TD]Cable Tie[/TD]
[TD]20[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]789[/TD]
[TD]100mm Screw[/TD]
[TD]40[/TD]
[/TR]
</tbody>[/TABLE]

My problem is I don't know how to make it so the picking slip only has the kits that are selected, not all the kits possible as there would be too many. And, for example, I need it to be able to identify that there are 2 x 21.1550 kits so we will need double the components to be picked.

Your help would be greatly appreciated



Order Table:
[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]QUANTITY[/TD]
[TD]CODE[/TD]
[TD]PRICE[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]21.1350[/TD]
[TD]82.00[/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD]21.1450[/TD]
[TD]136.00[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]21.1550[/TD]
[TD]190.00[/TD]
[/TR]
</tbody>[/TABLE]









Components Table:
[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]21.1350[/TD]
[TD]21.1450[/TD]
[TD]21.1550[/TD]
[/TR]
[TR]
[TD]Code[/TD]
[TD]Description[/TD]
[TD]Price[/TD]
[TD]1350mm Pipe[/TD]
[TD]1450mm Pipe[/TD]
[TD]1550 Pipe[/TD]
[/TR]
[TR]
[TD]123[/TD]
[TD]Pipe[/TD]
[TD]50.00[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]456[/TD]
[TD]Cable Tie[/TD]
[TD]2.00[/TD]
[TD]6[/TD]
[TD]8[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]789[/TD]
[TD]100mm Screw[/TD]
[TD]1.00[/TD]
[TD]20[/TD]
[TD]20[/TD]
[TD]20[/TD]
[/TR]
</tbody>[/TABLE]