Create a list of bought-out items for various assemblies

[aimeelou2006]

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

 

[konew1]

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.

 

[aimeelou2006]

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.

 

[konew1]

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
	A	B	C	D	E	F	G
1	*	*	Item components of each assembly	*	*	*	*
2	Assembly	Planned Production	Item1	Item2	Item3	Item4	etc
3	AA	11	1	*	2	*	*
4	AB	12	2	*	1	*	*
5	AC	8	*	*	*	1	*
6	etc	*	*	7	*	*	*
7	*	*	*	*	*	*	*
8	*	*	*	*	*	*	*
9	control row1	*	1	0	2	3	*
10	control row2	*	Item1	*	Item3	Item4	*
11	Total Quantity Req'd	*	35	0	34	8	*
matrix

Excel Workbook
	A	B	C
1	ITEM	DESCRIPTION	COST each
2	Item1	Apple	5
3	Item2	Banana	5
4	Item3	vegetable	4
5	Item4	Tomato	3
6	etc	strawberry	2
Item List

Excel Workbook
	A	B	C	D	E	F
1	Number of different ITEM types required this week	*	*	*	*	3
2	*	*	*	*	*	*
3	*	Item Code	Description	Cost each	Qty	total cost
4	1	Item1	Apple	5	35	175
5	2	Item3	vegetable	4	34	136
6	3	Item4	Tomato	3	8	24
7	4	*	*	*	*	*
8	5	*	*	*	*	*
9	ok	*	*	*	*	*
Summary

Edit: On Summary! the description in A1 refers to data in F1. Jeanie played around with my formatting.

 

[aimeelou2006]

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.

 

[konew1]

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.