Create a list of bought-out items for various assemblies


New Member
Sep 6, 2012
I'm trying to create a list of bough-out items for various assemblies I'm making from week to week. These assemblies are called AA, AB, AC, etc through to ZZ, so as you can imagine there are quite alot. I do not use the same assemblies each week, so no week is the same. Each assembly has between 1 & 12 bough-out items per assembly, some of which are common to other assemblies. So what I'm trying to do is type into an excel spreadsheet which assemblies I want next week inorder to get a full list of bough-out items for that week. I did originally think of doing it by filters, but I think it could get a bit messy and lead to mistakes when ticking which assemblies, so what I though was; if I physically type each assembly into a spreadsheet with corresponding quantities this should reduce the chance of making mistakes. End result...... a list of bought-out items with quantities

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
I think you need a sheet with all the assemblies listed down column A and all the items listed across in row 1 starting in column C (Leave B blank for now). On the each assembly row enter the quantity required of each item ( if zero then can leave it blank).

After setting up the matrix described above use sumproduct to calculate the number of each item.

There are lots of ways of setting up the weekly input for you to enter the planned production, of each item required.

The easiest way is to use column B to enter your planned production numbers. Under the static data in the matrix leave a blank row then use sumproduct to show the number of each item required that week.
Last edited:
Upvote 0
Thank you for your comments, although after researching the tutorials on sumproduct I can't see that this is the way forward, unless I missing something.
I do agree with you in that I will have to create the matrix first, which will take a lot of time so I need to get it right on paper before I proceed. I don't think I have explained myself properly, the resulting bought-out list needs to consist of "Item", "Quantity" & "Description" columns with the various item numbers proceeding down the sheet under the "Item" title. I do not need to reference the assembies on the final bought-out list. My initial thoughts were creating 3 sheets, firstly the matrix, secondly the input of what assemblies I require from week to week and then finally the resulting bought-out list which would be generated using formaulas. There are alot of assemblies so scrolling through filters or cells looking for the correct assemblies isn't a good option from my point of view, this is why I thought of inputting manually the assembly codes and the quantities to generate a list, rather than using filters. Please note- the assembly codes are not AA, AB, AC, etc, I have had to use these as an example for security reasons. Any help would be appreciated.
Upvote 0
Here is a small demo. Three sheets. MATRIX sheet serves two purposes, There is a lot of static data specifying all the Items in each Assembly. Once set up that part only changes as your products change. In column B each week you enter the planned production quantities of each assembly.
Sheet ITEM LIST is simply a list od all items you need to buy.
Sheet summary has only 5 rows, you will need to different items required in any week. I put in a warning mechanism.
Excel Workbook
1**Item components of each assembly****
2AssemblyPlanned ProductionItem1Item2Item3Item4etc
9control row1*1023*
10control row2*Item1*Item3Item4*
11Total Quantity Req'd*350348*

Excel Workbook
Item List

Excel Workbook
1Number of different ITEM types required this week****3
3*Item CodeDescriptionCost eachQtytotal cost

Edit: On Summary! the description in A1 refers to data in F1. Jeanie played around with my formatting.
Last edited:
Upvote 0
Thank you very much. I've set myself up with a dozen or so assemblies so far and it works great.
I'm understanding much better now how the formulas work too.
I can't thank you enough.
Upvote 0
Thanks for the feedback. The best time to understand the formulas is now, while the whole project is fresh in your mind. At some time in the future some aspect of your business will change and you will need to adapt the spread sheet. That will be much easier because you understand this one.
Upvote 0

Forum statistics

Latest member

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back