Stock position in the List box upon Factory selection

[Zubair]

Hi Experts,

I want Popup Stock availability (List Box or Square box) area from C2:G10 on each cell when the user selects Cell B2, B3, B4, and B5.

H1:AB7 = Factory wise stock
A12:D18 = Stock availability upon selection of Factory in cell B2

Please help.

Mr. Excel.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB
1	Form									Factory 1				Factory 2				Factory 3				Factory 4				Factory 5
2	Location	Factory 3						Size		Shirts	T Shirts	Caps		Shirts	T Shirts	Caps		Shirts	T Shirts	Caps		Shirts	T Shirts	Caps		Shirts	T Shirts	Caps
3	Brand	Caps						20		1	5	9		13	17	21		25	29	33		37	41	45		74	87	99
4	Size	22						21		2	6	10		14	18	22		26	30	34		38	42	46		76	84	92
5	Required Qty							22		3	7	11		15	19	23		27	31	35		39	43	47		78	86	94
6								23		4	8	12		16	20	24		28	32	36		40	44	48		80	88	96
7								24		4	8	12		16	20	24		28	32	36		40	44	48		80	88	96
8
9
10
11	Stock availability
12	Location	Factory 3
13	Size	Shirts	T Shirts	Caps
14	20	25	29	33
15	21	26	30	34
16	22	27	31	35
17	23	28	32	36
18	24	28	32	36
Stock Popup

Cell Formulas
Range	Formula
Z3,Z4:AB7	Z3	=+V3*2
B12	B12	=+B2
B14:D18	B14	=INDEX($J$3:$AB$7,MATCH($A14,$H$3:$H$7,0),MATCH($B$12,$J$1:$AB$1,0)+MATCH(B$13,$J$2:$L$2,0)-1)

 

[igold]

Have you considered storing your data differently so that you could use a Pivot Table and Slicers. I was not able to get XLBB to pickup the Slicers but you can see the PT with just one option selected from the Slicers...

Book1
	A	B	C	D	E	F	G
1	Factory	Brand	Size	Qty
2	Factory 1	Caps	20	9		Location	Brand
3	Factory 1	Caps	21	10		Factory/Size	Caps
4	Factory 1	Caps	22	11		Factory 3
5	Factory 1	Caps	23	12		21	34
6	Factory 1	Caps	24	12
7	Factory 1	Shirts	20	1
8	Factory 1	Shirts	21	2
9	Factory 1	Shirts	22	3
10	Factory 1	Shirts	23	4
11	Factory 1	Shirts	24	4
12	Factory 1	T Shirts	20	5
13	Factory 1	T Shirts	21	6
14	Factory 1	T Shirts	22	7
15	Factory 1	T Shirts	23	8
16	Factory 1	T Shirts	24	8
17	Factory 2	Caps	20	21
18	Factory 2	Caps	21	22
19	Factory 2	Caps	22	23
20	Factory 2	Caps	23	24
21	Factory 2	Caps	24	24
22	Factory 2	Shirts	20	13
23	Factory 2	Shirts	21	14
24	Factory 2	Shirts	22	15
25	Factory 2	Shirts	23	16
26	Factory 2	Shirts	24	16
27	Factory 2	T Shirts	20	17
28	Factory 2	T Shirts	21	18
29	Factory 2	T Shirts	22	19
30	Factory 2	T Shirts	23	20
31	Factory 2	T Shirts	24	20
32	Factory 3	Caps	20	33
33	Factory 3	Caps	21	34
34	Factory 3	Caps	22	35
35	Factory 3	Caps	23	36
36	Factory 3	Caps	24	36
37	Factory 3	Shirts	20	25
38	Factory 3	Shirts	21	26
39	Factory 3	Shirts	22	27
40	Factory 3	Shirts	23	28
41	Factory 3	Shirts	24	28
42	Factory 3	T Shirts	20	29
43	Factory 3	T Shirts	21	30
44	Factory 3	T Shirts	22	31
45	Factory 3	T Shirts	23	32
46	Factory 3	T Shirts	24	32
47	Factory 4	Caps	20	45
48	Factory 4	Caps	21	46
49	Factory 4	Caps	22	47
50	Factory 4	Caps	23	48
51	Factory 4	Caps	24	48
52	Factory 4	Shirts	20	37
53	Factory 4	Shirts	21	38
54	Factory 4	Shirts	22	39
55	Factory 4	Shirts	23	40
56	Factory 4	Shirts	24	40
57	Factory 4	T Shirts	20	41
58	Factory 4	T Shirts	21	42
59	Factory 4	T Shirts	22	43
60	Factory 4	T Shirts	23	44
61	Factory 4	T Shirts	24	44
62	Factory 5	Caps	20	99
63	Factory 5	Caps	21	92
64	Factory 5	Caps	22	94
65	Factory 5	Caps	23	96
66	Factory 5	Caps	24	96
67	Factory 5	Shirts	20	74
68	Factory 5	Shirts	21	76
69	Factory 5	Shirts	22	78
70	Factory 5	Shirts	23	80
71	Factory 5	Shirts	24	80
72	Factory 5	T Shirts	20	87
73	Factory 5	T Shirts	21	84
74	Factory 5	T Shirts	22	86
75	Factory 5	T Shirts	23	88
76	Factory 5	T Shirts	24	88
Sheet1

 

[Zubair]

Have you considered storing your data differently so that you could use a Pivot Table and Slicers. I was not able to get XLBB to pickup the Slicers but you can see the PT with just one option selected from the Slicers...

Book1
	A	B	C	D	E	F	G
1	Factory	Brand	Size	Qty
2	Factory 1	Caps	20	9		Location	Brand
3	Factory 1	Caps	21	10		Factory/Size	Caps
4	Factory 1	Caps	22	11		Factory 3
5	Factory 1	Caps	23	12		21	34
6	Factory 1	Caps	24	12
7	Factory 1	Shirts	20	1
8	Factory 1	Shirts	21	2
9	Factory 1	Shirts	22	3
10	Factory 1	Shirts	23	4
11	Factory 1	Shirts	24	4
12	Factory 1	T Shirts	20	5
13	Factory 1	T Shirts	21	6
14	Factory 1	T Shirts	22	7
15	Factory 1	T Shirts	23	8
16	Factory 1	T Shirts	24	8
17	Factory 2	Caps	20	21
18	Factory 2	Caps	21	22
19	Factory 2	Caps	22	23
20	Factory 2	Caps	23	24
21	Factory 2	Caps	24	24
22	Factory 2	Shirts	20	13
23	Factory 2	Shirts	21	14
24	Factory 2	Shirts	22	15
25	Factory 2	Shirts	23	16
26	Factory 2	Shirts	24	16
27	Factory 2	T Shirts	20	17
28	Factory 2	T Shirts	21	18
29	Factory 2	T Shirts	22	19
30	Factory 2	T Shirts	23	20
31	Factory 2	T Shirts	24	20
32	Factory 3	Caps	20	33
33	Factory 3	Caps	21	34
34	Factory 3	Caps	22	35
35	Factory 3	Caps	23	36
36	Factory 3	Caps	24	36
37	Factory 3	Shirts	20	25
38	Factory 3	Shirts	21	26
39	Factory 3	Shirts	22	27
40	Factory 3	Shirts	23	28
41	Factory 3	Shirts	24	28
42	Factory 3	T Shirts	20	29
43	Factory 3	T Shirts	21	30
44	Factory 3	T Shirts	22	31
45	Factory 3	T Shirts	23	32
46	Factory 3	T Shirts	24	32
47	Factory 4	Caps	20	45
48	Factory 4	Caps	21	46
49	Factory 4	Caps	22	47
50	Factory 4	Caps	23	48
51	Factory 4	Caps	24	48
52	Factory 4	Shirts	20	37
53	Factory 4	Shirts	21	38
54	Factory 4	Shirts	22	39
55	Factory 4	Shirts	23	40
56	Factory 4	Shirts	24	40
57	Factory 4	T Shirts	20	41
58	Factory 4	T Shirts	21	42
59	Factory 4	T Shirts	22	43
60	Factory 4	T Shirts	23	44
61	Factory 4	T Shirts	24	44
62	Factory 5	Caps	20	99
63	Factory 5	Caps	21	92
64	Factory 5	Caps	22	94
65	Factory 5	Caps	23	96
66	Factory 5	Caps	24	96
67	Factory 5	Shirts	20	74
68	Factory 5	Shirts	21	76
69	Factory 5	Shirts	22	78
70	Factory 5	Shirts	23	80
71	Factory 5	Shirts	24	80
72	Factory 5	T Shirts	20	87
73	Factory 5	T Shirts	21	84
74	Factory 5	T Shirts	22	86
75	Factory 5	T Shirts	23	88
76	Factory 5	T Shirts	24	88
Sheet1

Hi igold, many thanks for your quick help.

I have seen the popup in 1 of the training video of "Excel for freelancer" while selecting cell E12, E13.... the calender popup appears, similarly, I want a popup of stock availability A12:D18 while selecting cell B2, B3... B5.

Invoice_With_Profit.xlsm
	C	D	E	F	G	H	I	J	K	L	M	N
1							Invoice #	2	Search Invoice
2									INVOICE TOTALS
3							Invoice Date	1/31/2022	Service Sales	$ 200.00
4							Status	Open	Item Sales	$ 200.00
5				Customer:	Betty White				Service Cost	$ 59.22
6					Ap #556-4411 Rutrum Street				Item Cost	$ 150.00
7					Mobile Alabama, 35341				Total Profit	$ 190.78
8	ITEM ID.	ITEM TYPE	DATE	TECH./ITEM	DESCRIPTION	QTY	AMOUNT	TOTAL	ITEM COST	TOTAL COST
9	EngOil40D	Item	2/9/2022	Engine Oil	40D Engine Oil (Quart)	1.00	$20.00	$ 20.00	$10.00	$ 10.00
10	OilChng	Service	2/1/2022	Fred Fredders	Standard Oil Change	2.00	$100.00	$ 200.00	$29.61	$ 59.22
11	Tire-Good15	Item		15"GoodTire	15" Goodyear All Year Tire	4.00	$45.00	$ 180.00	$35.00	$ 140.00
12
13
14
15
16
17
18
19
20
21
22
23
24
25
Invoice

Cell Formulas
Range	Formula
L3	L3	=SUMIF(D9:D31,"Service",J9:J31)
L4	L4	=SUMIF(D9:D31,"Item",J9:J31)
L5	L5	=SUMIF(D9:D31,"Service",L9:L31)
L6	L6	=SUMIF(D9:D31,"Item",L9:L31)
L7	L7	=SUM(L3:L4)-SUM(L5:L6)
J9:J25	J9	=IF(AND(H9<>"",I9<>""),H9*I9,"")
L9:L25	L9	=IF(AND(H9<>"",K9<>""),H9*K9,"")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C9:D31,K9:L31	Expression	=AND($C9<>"",MOD(ROW(),2)=0)	text	NO

Cells with Data Validation
Cell	Allow	Criteria
G5	List	=Cust_Name
F12:F13	List	=Tech_Name
J4	List	=Status
C9:C25	List	=Item_ID

 

[igold]

Videos are nice. I do not know what your ultimate goal is, but looking at your post, If I were going to try to keep track of a running inventory and be able to "slice and dice" it in an easy manner, the last thing I would do is keep track of it going across the columns, rather than down the rows. It is just not a practical way to organize your data. You will eventually regret keeping your data in the manner which you now have it.

 

[Zubair]

Videos are nice. I do not know what your ultimate goal is, but looking at your post, If I were going to try to keep track of a running inventory and be able to "slice and dice" it in an easy manner, the last thing I would do is keep track of it going across the columns, rather than down the rows. It is just not a practical way to organize your data. You will eventually regret keeping your data in the manner which you now have it.

Actually, I want it for the user who is using sales form so selecting Factory, Brand & size will give him/her the current position of the stock. So Pivot table is not the appropriate choice.

 

[igold]

If you provide your user with Slicers for Factory, Brand, & Size, it is a good choice.