Hello,
I have data in multiple sheets before OUT sheet where run the form and I will add new sheets before OUT sheet.
the result on form
rest of picture
so should merge duplicates items (sales, sales returns, buying , buying returns, paid, received) in column C for each name in column B for each sheet contains name alone except OPENING BALANCE , in second column in listbox will brings balance from TOTAL row from BALANCE column (F), rest of columns will merge duplicates items
TOTAL row will sum each column . NET row will be subtract amounts in TOTAL row from each column
NET row will be :
SALES=8000-1200=6800 (subtract TOTAL sales from TOTAL sales returns)
BUYING =11000-6700=4300(subtract TOTAL buying from TOTAL buying returns)
RECEIVED=5100-900=4200(subtract TOTAL RECEIVED from TOTAL paid )
there is no more items , just as in sheets .
and without forgetting populate data based on dates in textbox1,2.
thanks
I have data in multiple sheets before OUT sheet where run the form and I will add new sheets before OUT sheet.
AL.xlsm | ||||||||
---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | |||
1 | DATE | NAME | DETAILES | DEBIT | CREDIT | BALANCE | ||
2 | 31/10/2024 | ALAA | OPENING BALANCE | 30,000.00 | 30,000.00 | |||
3 | 03/11/2024 | ALAA | BUYING RETURNS IBN3446 | 1,000.00 | 31,000.00 | |||
4 | 04/11/2024 | ALAA | PAID VDF10000 | 300.00 | 30,700.00 | |||
5 | 05/11/2024 | ALAA | SALES INVBN200 | 2,000.00 | 32,700.00 | |||
6 | 06/11/2024 | ALAA | BUYING IBN3444 | 10,000.00 | 22,700.00 | |||
7 | 07/11/2024 | ALAA | BUYING RETURNS IBN3445 | 3,000.00 | 25,700.00 | |||
8 | 08/11/2024 | ALAA | SALES INVBN201 | 2,000.00 | 27,700.00 | |||
9 | 09/11/2024 | ALAA | SALES RETURNS INVBN202 | 1,200.00 | 26,500.00 | |||
10 | 10/11/2024 | ALAA | RECEIVED VBF5666 | 1,100.00 | 27,600.00 | |||
11 | 11/11/2024 | ALAA | RECEIVED VBF5667 | 2,000.00 | 29,600.00 | |||
12 | TOTAL | 41,100.00 | 11,500.00 | 29,600.00 | ||||
SH |
Cell Formulas | ||
---|---|---|
Range | Formula | |
F3:F11 | F3 | =F2+D3-E3 |
D12:E12 | D12 | =SUM(D2:D11) |
F12 | F12 | =D12-E12 |
AL.xlsm | ||||||||
---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | |||
1 | DATE | NAME | DETAILES | DEBIT | CREDIT | BALANCE | ||
2 | 31/10/2024 | ALIAS | OPENING BALANCE | 10,000.00 | -10,000.00 | |||
3 | 02/11/2024 | ALIAS | SALES INVBN203 | 2,000.00 | -8,000.00 | |||
4 | 03/11/2024 | ALIAS | BUYING RETURNS IBN3449 | 1,200.00 | -6,800.00 | |||
5 | 04/11/2024 | ALIAS | PAID VDF10001 | 200.00 | -7,000.00 | |||
6 | 05/11/2024 | ALIAS | PAID VDF10002 | 200.00 | -7,200.00 | |||
7 | 06/11/2024 | ALIAS | PAID VDF10003 | 200.00 | -7,400.00 | |||
8 | 07/11/2024 | ALIAS | SALES INVBN204 | 2,000.00 | -5,400.00 | |||
9 | 08/11/2024 | ALIAS | BUYING IBN3447 | 1,000.00 | -6,400.00 | |||
10 | 09/11/2024 | ALIAS | BUYING RETURNS IBN3448 | 1,500.00 | -4,900.00 | |||
11 | 10/11/2024 | ALIAS | RECEIVED VBF5669 | 2,000.00 | -2,900.00 | |||
12 | TOTAL | 8,700.00 | 11,600.00 | -2,900.00 | ||||
ASD |
Cell Formulas | ||
---|---|---|
Range | Formula | |
F3:F11 | F3 | =F2+D3-E3 |
D12:E12 | D12 | =SUM(D2:D11) |
F12 | F12 | =D12-E12 |
the result on form
rest of picture
so should merge duplicates items (sales, sales returns, buying , buying returns, paid, received) in column C for each name in column B for each sheet contains name alone except OPENING BALANCE , in second column in listbox will brings balance from TOTAL row from BALANCE column (F), rest of columns will merge duplicates items
TOTAL row will sum each column . NET row will be subtract amounts in TOTAL row from each column
NET row will be :
SALES=8000-1200=6800 (subtract TOTAL sales from TOTAL sales returns)
BUYING =11000-6700=4300(subtract TOTAL buying from TOTAL buying returns)
RECEIVED=5100-900=4200(subtract TOTAL RECEIVED from TOTAL paid )
there is no more items , just as in sheets .
and without forgetting populate data based on dates in textbox1,2.
thanks