abdo meghari
Well-known Member
- Joined
- Aug 3, 2021
- Messages
- 570
- Office Version
- 2019
Hi experts
combobox1 should match with column B for each sheet then should brings data and populate in listbox1 :
1-in column (1) for listbox1 will show sheet name and brings data for old date across sheets .
2- in column (2)for listbox1 will brings INVOICE.N from each sheet is existed in column C based on adjacent sheet name in column (1) for listbox1
3- in column (3)for listbox1 will brings DATE from each sheet is existed in column A based on adjacent sheet name in column (1) for listbox1
4- in column (4) for listbox1 will brings CUSTOMER from each sheet is existed in column D based on adjacent sheet name in column (1) for listbox1.
5- in column (5) for row2 in listbox1 will brings QTY from STOCK sheet in Column (C) .
6-in column (6) for listbox1 will brings UNIT PRICE from STOCK sheet in Column (D) .
7- in column(7) will brings QTY from each sheet in column(E) based on adjacent sheet name in column (1) for listbox1
8- in column(8) will brings UNIT PRICE from each sheet in column(F) based on adjacent sheet name in column (1) for listbox1.
9- in column(9) will calculation QTY based on sheet name , if sheet name in column (1) for listbox1 is
BUYING then the column(9)=column(5) +column(7)
if sheet name in column (1)for listbox1 is
SELLING then the column(9)=column(5) - column(7), if sheet name in column (1) for listbox1 is BUYING RETURNS then the column(9)=column(5) - column(7) , if sheet name in column (1) for listbox1 is
SELLING RETURNS then the column(9)=column(5) +column(7)
10-column(10) should be the same column(8)
next row(3) depends on row2 then will move numeric vales from row2 to row 3 in listbox1
the values are existed in column 5,6 for next row will depends on previous row in columns 9,10
in column( 9) listbox1 for in row2 will copy to column( 5 )in row3 for listbox1 ,
in column( 8) in listbox1 for row2 will copy to column(6 )in row3 for listbox1 ,
in columns( 7,8) in listbox1 will brings QTY,PRICE from sheet name is matched with column (1) for row3 in listbox1 ,
in column( 9 ) will calculation QTY for row3(CHECK POINT 8) .
in column( 10 ) for row3 (check point9)
without forgetting add headers in row1 in listbox1
my data in each sheet will be
so when search for BRAND in combobox1 and match with column B across sheet will be
when select combobox1 the BRAND and write two dates in textbox1,textbox2 then will show data for brand based on dates.
when clear dates then should populate based on combobox1 if it's filled and if combobox1 is filled and bot textboxes are filled then will clear textbox until fill combobox1 and when bot textboxes and combobox are empty then don't show any thing in listbox when run the form .
I have data 8800 rows for each sheet as maximum .
I know this project is complex , if any detail is not clear just ask me .
thanks
combobox1 should match with column B for each sheet then should brings data and populate in listbox1 :
1-in column (1) for listbox1 will show sheet name and brings data for old date across sheets .
2- in column (2)for listbox1 will brings INVOICE.N from each sheet is existed in column C based on adjacent sheet name in column (1) for listbox1
3- in column (3)for listbox1 will brings DATE from each sheet is existed in column A based on adjacent sheet name in column (1) for listbox1
4- in column (4) for listbox1 will brings CUSTOMER from each sheet is existed in column D based on adjacent sheet name in column (1) for listbox1.
5- in column (5) for row2 in listbox1 will brings QTY from STOCK sheet in Column (C) .
6-in column (6) for listbox1 will brings UNIT PRICE from STOCK sheet in Column (D) .
7- in column(7) will brings QTY from each sheet in column(E) based on adjacent sheet name in column (1) for listbox1
8- in column(8) will brings UNIT PRICE from each sheet in column(F) based on adjacent sheet name in column (1) for listbox1.
9- in column(9) will calculation QTY based on sheet name , if sheet name in column (1) for listbox1 is
BUYING then the column(9)=column(5) +column(7)
if sheet name in column (1)for listbox1 is
SELLING then the column(9)=column(5) - column(7), if sheet name in column (1) for listbox1 is BUYING RETURNS then the column(9)=column(5) - column(7) , if sheet name in column (1) for listbox1 is
SELLING RETURNS then the column(9)=column(5) +column(7)
10-column(10) should be the same column(8)
next row(3) depends on row2 then will move numeric vales from row2 to row 3 in listbox1
the values are existed in column 5,6 for next row will depends on previous row in columns 9,10
in column( 9) listbox1 for in row2 will copy to column( 5 )in row3 for listbox1 ,
in column( 8) in listbox1 for row2 will copy to column(6 )in row3 for listbox1 ,
in columns( 7,8) in listbox1 will brings QTY,PRICE from sheet name is matched with column (1) for row3 in listbox1 ,
in column( 9 ) will calculation QTY for row3(CHECK POINT 8) .
in column( 10 ) for row3 (check point9)
without forgetting add headers in row1 in listbox1
my data in each sheet will be
1BRANDS V3.xlsm | |||||||||
---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | |||
1 | DATE | BRAND | INVOICE.N | CUSTOMER | QTY | UNIT PRICE | BALANCE | ||
2 | 01/01/2024 | BS 195R15C R623 JAP | SDF1100 | CRTE100 | 40.00 | 300.00 | 12,000.00 | ||
3 | 01/01/2024 | FS 195R15C R623 THI | SDF1100 | CRTE100 | 50.00 | 250.00 | 12,500.00 | ||
4 | 01/01/2024 | DT 385/65R22.5 DS53 THI | SDF1100 | CRTE100 | 20.00 | 1,600.00 | 32,000.00 | ||
5 | 01/01/2024 | BS 315/80R22.5 R184 JAP | SDF1100 | CRTE100 | 30.00 | 1,600.00 | 48,000.00 | ||
6 | 01/01/2024 | BS 315/80R22.5 R184 THI | SDF1101 | CRTE101 | 40.00 | 1,650.00 | 66,000.00 | ||
7 | 01/01/2024 | BS 1200R20 G580 JAP | SDF1101 | CRTE101 | 500.00 | 1,800.00 | 900,000.00 | ||
8 | 01/01/2024 | BS 750R16 R230 JAP | SDF1101 | CRTE101 | 200.00 | 750.00 | 150,000.00 | ||
9 | 01/01/2024 | BS 1200R24 G582 JAP | SDF1101 | CRTE101 | 500.00 | 2,200.00 | 1,100,000.00 | ||
10 | 02/02/2024 | BS 1200R20 G580 THI | SDF1102 | CRTE102 | 500.00 | 1,825.00 | 912,500.00 | ||
11 | 02/02/2024 | BS 1200R20 R187 THI | SDF1102 | CRTE102 | 600.00 | 1,700.00 | 1,020,000.00 | ||
12 | 02/02/2024 | BS 1200R20 R187 JAP | SDF1102 | CRTE102 | 500.00 | 1,650.00 | 825,000.00 | ||
13 | 02/02/2024 | BS 1200R24 G580 JAP | SDF1102 | CRTE102 | 700.00 | 2,350.00 | 1,645,000.00 | ||
14 | 02/02/2024 | BS 700R16 R230 JAP | SDF1102 | CRTE102 | 340.00 | 650.00 | 221,000.00 | ||
15 | 02/02/2024 | BS 750R16 VSJ JAP | SDF1102 | CRTE102 | 400.00 | 750.00 | 300,000.00 | ||
16 | 02/02/2024 | BS 1200R20 G580 JAP | SDF1102 | CRTE102 | 400.00 | 1,885.00 | 754,000.00 | ||
17 | 02/02/2024 | BS 750R16 VSJ JAP | SDF1102 | CRTE102 | 600.00 | 770.00 | 462,000.00 | ||
18 | 02/02/2024 | BS 750R16 VSJ JAP | SDF1103 | CRTE103 | 400.00 | 800.00 | 320,000.00 | ||
19 | 02/02/2024 | BS 1200R20 G580 JAP | SDF1103 | CRTE103 | 500.00 | 1,760.00 | 880,000.00 | ||
BUYING |
Cell Formulas | ||
---|---|---|
Range | Formula | |
G2:G19 | G2 | =E2*F2 |
1BRANDS V3.xlsm | |||||||||
---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | |||
1 | DATE | BRAND | INVOICE.N | CUSTOMER | QTY | UNIT PRICE | BALANCE | ||
2 | 01/11/2024 | BS 1200R20 G580 JAP | BST-100 | CMM100 | 200.00 | 2,000.00 | 400,000.00 | ||
3 | 01/11/2024 | BS 1200R20 G580 THI | BST-100 | CMM100 | 100.00 | 1,990.00 | 199,000.00 | ||
4 | 01/11/2024 | BS 1200R20 R187 THI | BST-100 | CMM100 | 50.00 | 2,100.00 | 105,000.00 | ||
5 | 01/11/2024 | BS 1200R20 R187 JAP | BST-100 | CMM100 | 100.00 | 2,200.00 | 220,000.00 | ||
6 | 01/11/2024 | BS 1200R24 G580 JAP | BST-100 | CMM100 | 100.00 | 2,500.00 | 250,000.00 | ||
7 | 01/11/2024 | BS 1200R24 G582 JAP | BST-101 | CMM101 | 120.00 | 2,600.00 | 312,000.00 | ||
8 | 02/12/2024 | BS 750R16 R230 JAP | BST-101 | CMM101 | 100.00 | 750.00 | 75,000.00 | ||
9 | 02/12/2024 | BS 700R16 R230 JAP | BST-101 | CMM101 | 120.00 | 730.00 | 87,600.00 | ||
10 | 02/12/2024 | BS 750R16 VSJ JAP | BST-101 | CMM101 | 100.00 | 900.00 | 90,000.00 | ||
11 | 02/12/2024 | BS 1200R20 G580 JAP | BST-102 | CMM102 | 120.00 | 2,100.00 | 252,000.00 | ||
12 | 02/12/2024 | BS 750R16 VSJ JAP | BST-102 | CMM102 | 50.00 | 920.00 | 46,000.00 | ||
13 | 02/12/2024 | BS 1200R20 G580 JAP | BST-103 | CMM103 | 120.00 | 2,120.00 | 254,400.00 | ||
14 | 02/12/2024 | BS 1200R20 G580 JAP | BST-104 | CMM104 | 100.00 | 2,110.00 | 211,000.00 | ||
15 | 02/12/2024 | BS 1200R20 R187 JAP | BST-104 | CMM104 | 200.00 | 2,130.00 | 426,000.00 | ||
16 | 02/12/2024 | BS 1200R24 G580 JAP | BST-104 | CMM104 | 10.00 | 2,450.00 | 24,500.00 | ||
17 | 02/12/2024 | BS 1200R20 G580 THI | BST-105 | CMM105 | 130.00 | 1,895.00 | 246,350.00 | ||
18 | 02/12/2024 | BS 1200R20 G580 JAP | BST-105 | CMM105 | 17.00 | 2,140.00 | 36,380.00 | ||
SELLING |
Cell Formulas | ||
---|---|---|
Range | Formula | |
G2:G18 | G2 | =E2*F2 |
1BRANDS V3.xlsm | |||||||||
---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | |||
1 | DATE | BRAND | INVOICE.N | CUSTOMER | QTY | UNIT PRICE | BALANCE | ||
2 | 01/20/2024 | DT 385/65R22.5 DS53 THI | DDT-100 | DDC-100 | 5.00 | 1,600.00 | 8,000.00 | ||
3 | 01/20/2024 | BS 315/80R22.5 R184 JAP | DDT-100 | DDC-100 | 5.00 | 1,600.00 | 8,000.00 | ||
4 | 01/20/2024 | BS 315/80R22.5 R184 THI | DDT-100 | DDC-100 | 4.00 | 1,650.00 | 6,600.00 | ||
5 | 01/20/2024 | DT 385/65R22.5 DS53 THI | DDT-101 | DDC-101 | 1.00 | 1,600.00 | 1,600.00 | ||
6 | 01/20/2024 | BS 315/80R22.5 R184 JAP | DDT-101 | DDC-101 | 2.00 | 1,600.00 | 3,200.00 | ||
7 | 01/20/2024 | BS 315/80R22.5 R184 THI | DDT-101 | DDC-101 | 1.00 | 1,650.00 | 1,650.00 | ||
8 | 01/20/2024 | BS 1200R20 G580 THI | DDT-101 | CRTE102 | 20.00 | 1,825.00 | 36,500.00 | ||
9 | 01/20/2024 | BS 1200R20 R187 THI | DDT-101 | CRTE102 | 4.00 | 1,700.00 | 6,800.00 | ||
10 | 02/21/2024 | BS 1200R20 R187 JAP | DDT-101 | CRTE102 | 10.00 | 1,650.00 | 16,500.00 | ||
11 | 02/21/2024 | BS 1200R20 G580 JAP | DDT-101 | CRTE102 | 20.00 | 2,350.00 | 47,000.00 | ||
12 | 02/21/2024 | BS 700R16 R230 JAP | DDT-101 | CRTE102 | 20.00 | 650.00 | 13,000.00 | ||
13 | 02/21/2024 | BS 750R16 VSJ JAP | DDT-101 | CRTE102 | 15.00 | 750.00 | 11,250.00 | ||
BUYING RETURNS |
Cell Formulas | ||
---|---|---|
Range | Formula | |
G2:G13 | G2 | =E2*F2 |
1BRANDS V3.xlsm | |||||||||
---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | |||
1 | DATE | BRAND | INVOICE.N | CUSTOMER | QTY | UNIT PRICE | BALANCE | ||
2 | 01/11/2024 | BS 1200R20 G580 JAP | FST-212 | FSST-123 | 10.00 | 2,000.00 | 20,000.00 | ||
3 | 01/11/2024 | BS 1200R20 G580 THI | FST-212 | FSST-123 | 10.00 | 1,990.00 | 19,900.00 | ||
4 | 02/22/2024 | BS 700R16 R230 JAP | FST-212 | FSST-123 | 12.00 | 730.00 | 8,760.00 | ||
5 | 02/22/2024 | BS 750R16 VSJ JAP | FST-212 | FSST-123 | 10.00 | 900.00 | 9,000.00 | ||
6 | 02/22/2024 | BS 750R16 R230 JAP | FST-212 | FSST-123 | 10.00 | 750.00 | 7,500.00 | ||
7 | 02/22/2024 | BS 1200R20 R187 THI | FST-212 | FSST-123 | 4.00 | 2,100.00 | 8,400.00 | ||
8 | 02/22/2024 | BS 1200R20 R187 JAP | FST-212 | FSST-123 | 4.00 | 2,200.00 | 8,800.00 | ||
9 | 02/22/2024 | BS 1200R24 G580 JAP | FST-212 | FSST-123 | 4.00 | 2,500.00 | 10,000.00 | ||
10 | 02/22/2024 | BS 1200R20 G580 JAP | FST-212 | FSST-123 | 2.00 | 2,600.00 | 5,200.00 | ||
SELLING RETURNS |
Cell Formulas | ||
---|---|---|
Range | Formula | |
G2:G10 | G2 | =E2*F2 |
1BRANDS V3.xlsm | |||||||
---|---|---|---|---|---|---|---|
A | B | C | D | E | |||
1 | ITEM | BRAND | QTY | UNIT PRICE | QTY | ||
2 | 1 | BS 195R15C R623 JAP | 450.00 | 180.00 | 81,000.00 | ||
3 | 2 | FS 195R15C R623 THI | 700.00 | 190.00 | 133,000.00 | ||
4 | 3 | DT 385/65R22.5 DS53 THI | 800.00 | 1,400.00 | 1,120,000.00 | ||
5 | 4 | BS 315/80R22.5 R184 JAP | 900.00 | 1,250.00 | 1,125,000.00 | ||
6 | 5 | BS 315/80R22.5 R184 THI | 1,000.00 | 1,225.00 | 1,225,000.00 | ||
7 | 6 | BS 1200R20 G580 JAP | 200.00 | 2,035.00 | 407,000.00 | ||
8 | 7 | BS 750R16 R230 JAP | 120.00 | 550.00 | 66,000.00 | ||
9 | 8 | BS 1200R24 G582 JAP | 60.00 | 2,200.00 | 132,000.00 | ||
10 | 9 | BS 1200R20 G580 THI | 50.00 | 2,000.00 | 100,000.00 | ||
11 | 10 | BS 1200R20 R187 THI | 40.00 | 1,770.00 | 70,800.00 | ||
12 | 11 | BS 1200R20 R187 JAP | 30.00 | 1,780.00 | 53,400.00 | ||
13 | 12 | BS 1200R24 G580 JAP | 20.00 | 2,125.00 | 42,500.00 | ||
14 | 13 | BS 700R16 R230 JAP | 25.00 | 450.00 | 11,250.00 | ||
15 | 14 | BS 750R16 VSJ JAP | 45.00 | 650.00 | 29,250.00 | ||
16 | 15 | BS 650R16 R230 JAP | 80.00 | 500.00 | 40,000.00 | ||
17 | 16 | BS 205/70R15C R623 THI | 90.00 | 400.00 | 36,000.00 | ||
18 | 17 | BS 195/65R15 TG90 JAP | 100.00 | 205.00 | 20,500.00 | ||
19 | 18 | BS 225/70R15C R623 JAP | 200.00 | 350.00 | 70,000.00 | ||
STOCK |
Cell Formulas | ||
---|---|---|
Range | Formula | |
E2:E19 | E2 | =C2*D2 |
so when search for BRAND in combobox1 and match with column B across sheet will be
when select combobox1 the BRAND and write two dates in textbox1,textbox2 then will show data for brand based on dates.
when clear dates then should populate based on combobox1 if it's filled and if combobox1 is filled and bot textboxes are filled then will clear textbox until fill combobox1 and when bot textboxes and combobox are empty then don't show any thing in listbox when run the form .
I have data 8800 rows for each sheet as maximum .
I know this project is complex , if any detail is not clear just ask me .
thanks