Stock position in the List box upon Factory selection

Zubair

Active Member
Joined
Jul 4, 2009
Messages
304
Office Version
  1. 2016
Platform
  1. Windows
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
ABCDEFGHIJKLMNOPQRSTUVWXYZAAAB
1FormFactory 1Factory 2Factory 3Factory 4Factory 5
2LocationFactory 3SizeShirtsT ShirtsCapsShirtsT ShirtsCapsShirtsT ShirtsCapsShirtsT ShirtsCapsShirtsT ShirtsCaps
3BrandCaps20159131721252933374145748799
4Size22212610141822263034384246768492
5Required Qty223711151923273135394347788694
6234812162024283236404448808896
7244812162024283236404448808896
8
9
10
11Stock availability
12LocationFactory 3
13SizeShirtsT ShirtsCaps
1420252933
1521263034
1622273135
1723283236
1824283236
Stock Popup
Cell Formulas
RangeFormula
Z3,Z4:AB7Z3=+V3*2
B12B12=+B2
B14:D18B14=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)
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
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
ABCDEFG
1FactoryBrandSizeQty
2Factory 1Caps209LocationBrand
3Factory 1Caps2110Factory/SizeCaps
4Factory 1Caps2211Factory 3
5Factory 1Caps23122134
6Factory 1Caps2412
7Factory 1Shirts201
8Factory 1Shirts212
9Factory 1Shirts223
10Factory 1Shirts234
11Factory 1Shirts244
12Factory 1T Shirts205
13Factory 1T Shirts216
14Factory 1T Shirts227
15Factory 1T Shirts238
16Factory 1T Shirts248
17Factory 2Caps2021
18Factory 2Caps2122
19Factory 2Caps2223
20Factory 2Caps2324
21Factory 2Caps2424
22Factory 2Shirts2013
23Factory 2Shirts2114
24Factory 2Shirts2215
25Factory 2Shirts2316
26Factory 2Shirts2416
27Factory 2T Shirts2017
28Factory 2T Shirts2118
29Factory 2T Shirts2219
30Factory 2T Shirts2320
31Factory 2T Shirts2420
32Factory 3Caps2033
33Factory 3Caps2134
34Factory 3Caps2235
35Factory 3Caps2336
36Factory 3Caps2436
37Factory 3Shirts2025
38Factory 3Shirts2126
39Factory 3Shirts2227
40Factory 3Shirts2328
41Factory 3Shirts2428
42Factory 3T Shirts2029
43Factory 3T Shirts2130
44Factory 3T Shirts2231
45Factory 3T Shirts2332
46Factory 3T Shirts2432
47Factory 4Caps2045
48Factory 4Caps2146
49Factory 4Caps2247
50Factory 4Caps2348
51Factory 4Caps2448
52Factory 4Shirts2037
53Factory 4Shirts2138
54Factory 4Shirts2239
55Factory 4Shirts2340
56Factory 4Shirts2440
57Factory 4T Shirts2041
58Factory 4T Shirts2142
59Factory 4T Shirts2243
60Factory 4T Shirts2344
61Factory 4T Shirts2444
62Factory 5Caps2099
63Factory 5Caps2192
64Factory 5Caps2294
65Factory 5Caps2396
66Factory 5Caps2496
67Factory 5Shirts2074
68Factory 5Shirts2176
69Factory 5Shirts2278
70Factory 5Shirts2380
71Factory 5Shirts2480
72Factory 5T Shirts2087
73Factory 5T Shirts2184
74Factory 5T Shirts2286
75Factory 5T Shirts2388
76Factory 5T Shirts2488
Sheet1
 
Upvote 0
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
ABCDEFG
1FactoryBrandSizeQty
2Factory 1Caps209LocationBrand
3Factory 1Caps2110Factory/SizeCaps
4Factory 1Caps2211Factory 3
5Factory 1Caps23122134
6Factory 1Caps2412
7Factory 1Shirts201
8Factory 1Shirts212
9Factory 1Shirts223
10Factory 1Shirts234
11Factory 1Shirts244
12Factory 1T Shirts205
13Factory 1T Shirts216
14Factory 1T Shirts227
15Factory 1T Shirts238
16Factory 1T Shirts248
17Factory 2Caps2021
18Factory 2Caps2122
19Factory 2Caps2223
20Factory 2Caps2324
21Factory 2Caps2424
22Factory 2Shirts2013
23Factory 2Shirts2114
24Factory 2Shirts2215
25Factory 2Shirts2316
26Factory 2Shirts2416
27Factory 2T Shirts2017
28Factory 2T Shirts2118
29Factory 2T Shirts2219
30Factory 2T Shirts2320
31Factory 2T Shirts2420
32Factory 3Caps2033
33Factory 3Caps2134
34Factory 3Caps2235
35Factory 3Caps2336
36Factory 3Caps2436
37Factory 3Shirts2025
38Factory 3Shirts2126
39Factory 3Shirts2227
40Factory 3Shirts2328
41Factory 3Shirts2428
42Factory 3T Shirts2029
43Factory 3T Shirts2130
44Factory 3T Shirts2231
45Factory 3T Shirts2332
46Factory 3T Shirts2432
47Factory 4Caps2045
48Factory 4Caps2146
49Factory 4Caps2247
50Factory 4Caps2348
51Factory 4Caps2448
52Factory 4Shirts2037
53Factory 4Shirts2138
54Factory 4Shirts2239
55Factory 4Shirts2340
56Factory 4Shirts2440
57Factory 4T Shirts2041
58Factory 4T Shirts2142
59Factory 4T Shirts2243
60Factory 4T Shirts2344
61Factory 4T Shirts2444
62Factory 5Caps2099
63Factory 5Caps2192
64Factory 5Caps2294
65Factory 5Caps2396
66Factory 5Caps2496
67Factory 5Shirts2074
68Factory 5Shirts2176
69Factory 5Shirts2278
70Factory 5Shirts2380
71Factory 5Shirts2480
72Factory 5T Shirts2087
73Factory 5T Shirts2184
74Factory 5T Shirts2286
75Factory 5T Shirts2388
76Factory 5T Shirts2488
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
CDEFGHIJKLMN
1Invoice #2Search Invoice
2INVOICE TOTALS
3Invoice Date1/31/2022Service Sales$ 200.00
4StatusOpenItem Sales$ 200.00
5Customer:Betty WhiteService Cost$ 59.22
6Ap #556-4411 Rutrum StreetItem Cost$ 150.00
7Mobile Alabama, 35341Total Profit$ 190.78
8ITEM ID.ITEM TYPEDATETECH./ITEMDESCRIPTIONQTYAMOUNTTOTALITEM COSTTOTAL COST
9EngOil40DItem2/9/2022Engine Oil40D Engine Oil (Quart)1.00$20.00$ 20.00$10.00$ 10.00
10OilChngService2/1/2022Fred FreddersStandard Oil Change2.00$100.00$ 200.00$29.61$ 59.22
11Tire-Good15Item15"GoodTire15" Goodyear All Year Tire4.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
RangeFormula
L3L3=SUMIF(D9:D31,"Service",J9:J31)
L4L4=SUMIF(D9:D31,"Item",J9:J31)
L5L5=SUMIF(D9:D31,"Service",L9:L31)
L6L6=SUMIF(D9:D31,"Item",L9:L31)
L7L7=SUM(L3:L4)-SUM(L5:L6)
J9:J25J9=IF(AND(H9<>"",I9<>""),H9*I9,"")
L9:L25L9=IF(AND(H9<>"",K9<>""),H9*K9,"")
Cells with Conditional Formatting
CellConditionCell FormatStop If True
C9:D31,K9:L31Expression=AND($C9<>"",MOD(ROW(),2)=0)textNO
Cells with Data Validation
CellAllowCriteria
G5List=Cust_Name
F12:F13List=Tech_Name
J4List=Status
C9:C25List=Item_ID


 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
If you provide your user with Slicers for Factory, Brand, & Size, it is a good choice.
 
Upvote 0

Forum statistics

Threads
1,223,246
Messages
6,170,987
Members
452,373
Latest member
TimReeks

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 MrExcel.com.
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 "mrexcel.com".
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
Back
Top