Production Table Help

[TheDarkLord]

i have an Excel table - Table1, with quantities of different ingredients used by batch for different products. the products are the column headings and the ingredients are the rows.

I have created another table - Table 2, with quantities to be made for each product in the top row. by multiplying these quantities with amount of ingredients for each product, i get the total ingredients to be used.

I would like to be able to get the list of ingredients by clicking a particular product, which would be like a material requisition list for that product.

kindly advise


table1: formula grid

...........product1........product2......product3.....product4......product5

ing1

ing2

ing3

ing4

ing5



table2 : compiled quantities of ingredients for quantity of product to be made.

.............................p1...........p2...........p3..........p4..............p5

qty
to be produced..........x ............x.............x.............x..............x

ing1

ing2

ing3

ing4

ing5

If i want to know the quantities of all ingredients to make product 1, i only have to click on button p1, and it will give me the list with the ingredient name and quantities.

kindly advise.

 

[Cindy Ellis]

Are the 2 tables in the same worksheet? In the same workbook?
You could just have the formulas in the cells in table 2, and change the quantity to a non-zero value to get the list for a specific quantity of a product, unless you really want to use a button?
Cindy

 

[TheDarkLord]

Yes, the two tables are on the same sheet, but I would just like to see the list of ingredients for a selected product only, as there are hundreds of products.

thanks

 

[Cindy Ellis]

Still clarifying (not intending to be confrontational)...
So, you would have hundreds of buttons, one for each product?

 

[TheDarkLord]

Technically yes, unless there's a better option

 

[Edleets]

Do you really need the hundreds of buttons at the same time...? Why not just have one button for all products where you enter the desired product and do a lookup to get the ingredients of that product?

Ed.

 

[gardnertoo]

Or, a single cell with Validation (found under the Data menu), using the list of products as your source, and "In cell dropdown" enabled.

 

[TheDarkLord]

Do you really need the hundreds of buttons at the same time...? Why not just have one button for all products where you enter the desired product and do a lookup to get the ingredients of that product?

Ed.

This looks interesting, how would I proceed?

 

[TheDarkLord]

Or, a single cell with Validation (found under the Data menu), using the list of products as your source, and "In cell dropdown" enabled.

How does this one work? Kindly Advise

 

[gardnertoo]

It could look something like this:

Book1
	A	B	C	D	E	F	G
1		Product 1	Product 2	Product 3		Product 2
2	Ing 1	3 lb	3 lb	3.2 lb		3 lb	Ing 1
3	Ing2		1.5 lb			1.5 lb	Ing 2
4	Ing 3		5 oz	6oz		5 oz	Ing 3
5	Ing 4						Ing 4
6	Ing 5						Ing 5
7	Ing 6		18 lb			18 lb	Ing 6
8	Ing 7	2 oz					Ing 7
9	Ing 8			12 gal			Ing 8
10	Ing 9		21 gal	12 gal		21 gal	Ing 9
11	Ing 10	23 gal					Ing 10
12	Ing 11						Ing 11
13	Ing 12			1.1 oz			Ing 12
14	Ing 13	1.4 oz	1.4 oz			1.4 oz	Ing 13
15	Ing 14			2 tsp			Ing 14
16	Ing 15						Ing 15
Sheet1

To build it, select cell F1 (in this example). Click the "Data" menu item, then in the dropdown that appears choose "Validation..." This will launch a dialog box, which (on my machine) opens to the "Settings" tab. The first item, "Allow" has a dropdown: choose "List". The third item allows you to specify where this "list" is. Click the button on the right, then highlight in the spreadsheet the items in the Product row. Make sure the "In cell dropdown" radio button is checked, then click OK. Now, when a user clicks on cell F1, a dropdown arrow appears. Click that, and the list you specified appears for them to pick one. You are also allowed to type a value in the cell, and if that typed value is not in the list an error dialog box pops.

This functionality of Excel can be very useful, but there is one thing to note: your "list" must be on the same sheet if you are specifying it by address. However, if you use named ranges, you can specify the name of the list even if that list is on a different sheet. Named range, when correctly used, also has an added bonus: if you add products later you don't have to remember to go back and add the new products to the validation list.