Calculate Number Needed to Build Kitted Order

If I understand correctly, enter this formula in cell I5 and copy down

=ROUNDUP( SUMPRODUCT(TRANSPOSE($B$4:$B$8)*XLOOKUP(D5,'"BOX" DATA SET '!$E$5:$E$32,'"BOX" DATA SET '!$L$5:$P$32)) / H5, 0)

I did a couple of things. First of all, I converted everything to Excel Tables. There are too many good reasons for using tables to avoid using them. Secondly, I consolidated all the information to one sheet. Then I "normalized" the data for how many of each item goes into each kit.

Here are the tables:

Columns A:B - Table_Orders. I use the kit name column for data validation on the Table_Required Kit column (described below).

Columns D:L - Table_SKU. This is the table you will maintain to add new IDs and Cost / Sell. I looked up the cost and sell from your other sheet and basically copied / pasted the data in so I could eliminate that tabl and sheet.

Qty Needed has the formula: =SUMIFS(Table_Required[Required],Table_Required[SKU],[@ProductID]) - this formula shows one of the advantages of tables: you can use table column names in the formulas which makes them easier to understand and maintain.

UOM Required is how many of the item by Unit of Measure you have to buy. For example if the total quality needed is 41, but the item comes in boxes of 10, then you need 5 boxes, You'll have 9 left over. The formula for this is: =ROUNDUP([@[QTY NEEDED]]/[@[BX QTY]],0).

Columns N:T - Table_Required. This table does the calculations. The first column, Line, isn't used anywhere. One of the other benefits of tables is that when you add a new row right under the table, it becomes part of the table. So formulas, validations and formats (even conditional formats) are copied down automatically. So typing ANYTHING in this cell opens up a new row in the table and the next cell has a dropdown for the kit name and the lookups and formulas for the last 4 columns are copied down automatically.

Description is looked up from the SKU table based on the SKU.

Quantity is from your previous table - "exploding" the former table like this is known as normalizing the data. It is how many of that item do you need for that kit.

Ordered is looked up from Table_Ordered based on the kit name.

Required =[@Quanity]*[@Ordered]