abdo meghari
Well-known Member
- Joined
- Aug 3, 2021
- Messages
- 711
- Office Version
- 2019
Hello,
before LIST sheet contains many sheets and increasable as I need it.
in list sheet will put PURCHASING ,SELLING RETURNS in credit column(D), SELLING ,PURCHASING RETURNS put in DEBIT column(C) as to RECEIVED should reverse to CREDIT column ,PAID to DEBIT column .
as to words in column B will brings after colon for each MOVEMENT word. when brings any word will be consecutive when move to next sheet until reach new movement.
the amounts will brings from TOTAL column , in first row will write date (end of date for previous year based on current year) BALANCE and if the amount is minus in A1 cell then will put in CREDIT column and also BALANCE column , if positive value in A1 cell then will put in DEBIT column and also BALANCE column after that will calculate for each next row in column DEBIT,CREDIT as formula shows .
RESULT SHOULD BE IN LIST sheet
before LIST sheet contains many sheets and increasable as I need it.
in list sheet will put PURCHASING ,SELLING RETURNS in credit column(D), SELLING ,PURCHASING RETURNS put in DEBIT column(C) as to RECEIVED should reverse to CREDIT column ,PAID to DEBIT column .
as to words in column B will brings after colon for each MOVEMENT word. when brings any word will be consecutive when move to next sheet until reach new movement.
the amounts will brings from TOTAL column , in first row will write date (end of date for previous year based on current year) BALANCE and if the amount is minus in A1 cell then will put in CREDIT column and also BALANCE column , if positive value in A1 cell then will put in DEBIT column and also BALANCE column after that will calculate for each next row in column DEBIT,CREDIT as formula shows .
LIST.xlsm | |||||||||
---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | |||
1 | BALANCE:-2,50000.00 | NAME:BASHIR | |||||||
2 | |||||||||
3 | MOVEMENT : PURCHASING | ||||||||
4 | DATE | INVOICE NO | CODE | BRAND | QTY | UNIT PRICE | TOTAL | ||
5 | 2024.01.02 | 114 | 1284 | GC 1200R20 AZ0026 CHI | 80 | 895 | 242560 | ||
6 | 1285 | GC 1200R20 AZ0183 CHI | 40 | 925 | |||||
7 | 1385 | GC 315/80R22.5 AT161 CHI | 20 | 735 | |||||
8 | 1287 | GC 315/80R22.5 AZ126 CHI | 20 | 735 | |||||
9 | 1294 | GC 315/80R22.5 AZ188 CHI | 20 | 745 | |||||
10 | 1241 | BS 1200R20 G580 JAP | 40 | 2035 | |||||
11 | 1227 | BS 215/70R15C R623 THI | 8 | 425 | |||||
12 | 1221 | BS 205/70R15C R623 THI | 12 | 405 | |||||
13 | DATE | INVOICE NO | CODE | BRAND | QTY | UNIT PRICE | TOTAL | ||
14 | 2024.01.09 | 121 | 1287 | GC 315/80R22.5 AZ126 CHI | 14 | 735 | 10290 | ||
15 | DATE | INVOICE NO | CODE | BRAND | QTY | UNIT PRICE | TOTAL | ||
16 | 2024.01.10 | 125 | 1227 | BS 215/70R15C R623 THI | 2 | 425 | 850 | ||
17 | DATE | INVOICE NO | CODE | BRAND | QTY | UNIT PRICE | TOTAL | ||
18 | 2024.01.20 | 148 | 1287 | GC 315/80R22.5 AZ126 CHI | 26 | 735 | 19110 | ||
19 | DATE | INVOICE NO | CODE | BRAND | QTY | UNIT PRICE | TOTAL | ||
20 | 2024.01.20 | 149 | 1221 | BS 205/70R15C R623 THI | 50 | 405 | 391650 | ||
21 | 1227 | BS 215/70R15C R623 THI | 12 | 425 | |||||
22 | 1241 | BS 1200R20 G580 JAP | 180 | 2035 | |||||
23 | DATE | INVOICE NO | CODE | BRAND | QTY | UNIT PRICE | TOTAL | ||
24 | 2024.03.30 | 217 | 1285 | GC 1200R20 AZ0183 CHI | 10 | 1225 | 12250 | ||
25 | DATE | INVOICE NO | CODE | BRAND | QTY | UNIT PRICE | TOTAL | ||
26 | 2024.03.30 | 226 | 1227 | BS 215/70R15C R623 THI | 4 | 544 | 2176 | ||
27 | DATE | INVOICE NO | CODE | BRAND | QTY | UNIT PRICE | TOTAL | ||
28 | 2024.04.14 | 230 | 1285 | GC 1200R20 AZ0183 CHI | 40 | 1205 | 81950 | ||
29 | 1284 | GC 1200R20 AZ0026 CHI | 30 | 1125 | |||||
30 | DATE | INVOICE NO | CODE | BRAND | QTY | UNIT PRICE | TOTAL | ||
31 | 2024.04.17 | 236 | 1284 | GC 1200R20 AZ0026 CHI | 20 | 1125 | 46600 | ||
32 | 1285 | GC 1200R20 AZ0183 CHI | 20 | 1205 | |||||
PAGE 0 |
LIST.xlsm | |||||||||
---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | |||
1 | DATE | INVOICE NO | CODE | BRAND | QTY | UNIT PRICE | TOTAL | ||
2 | 2024.01.021 | 566 | 1284 | GC 1200R20 AZ0026 CHI | 10 | 895 | 25550 | ||
3 | 1285 | GC 1200R20 AZ0183 CHI | 10 | 925 | |||||
4 | 1385 | GC 315/80R22.5 AT161 CHI | 10 | 735 | |||||
5 | DATE | INVOICE NO | CODE | BRAND | QTY | UNIT PRICE | TOTAL | ||
6 | 2024.04.17 | 567 | 1287 | GC 315/80R22.5 AZ126 CHI | 1 | 735 | 735 | ||
7 | DATE | INVOICE NO | CODE | BRAND | QTY | UNIT PRICE | TOTAL | ||
8 | 2024.04.18 | 568 | 1227 | BS 215/70R15C R623 THI | 10 | 425 | 4250 | ||
9 | DATE | INVOICE NO | CODE | BRAND | QTY | UNIT PRICE | TOTAL | ||
10 | 2024.01.021 | 569 | 1287 | GC 315/80R22.5 AZ126 CHI | 1 | 735 | 735 | ||
11 | MOVEMENT : SELLING | ||||||||
12 | DATE | INVOICE NO | CODE | BRAND | QTY | UNIT PRICE | TOTAL | ||
13 | 2024.01.24 | 440 | 1306 | BS 750R16 R230 JAP | 10 | 775 | 7750 | ||
14 | DATE | INVOICE NO | CODE | BRAND | QTY | UNIT PRICE | TOTAL | ||
15 | 2024.01.24 | 441 | 1306 | BS 750R16 R230 JAP | 2 | 780 | 1560 | ||
16 | DATE | INVOICE NO | CODE | BRAND | QTY | UNIT PRICE | TOTAL | ||
17 | 2024.01.24 | 442 | 1306 | BS 750R16 R230 JAP | 1 | 780 | 2285 | ||
18 | 1305 | BS 700R16 R230 JAP | 1 | 770 | |||||
19 | 1287 | GC 315/80R22.5 AZ126 CHI | 1 | 735 | |||||
20 | DATE | INVOICE NO | CODE | BRAND | QTY | UNIT PRICE | TOTAL | ||
21 | 2024.01.25 | 443 | 1227 | BS 215/70R15C R623 THI | 2 | 425 | 850 | ||
22 | DATE | INVOICE NO | CODE | BRAND | QTY | UNIT PRICE | TOTAL | ||
23 | 2024.01.25 | 444 | 1287 | GC 315/80R22.5 AZ126 CHI | 1 | 735 | 735 | ||
PAGE 1 |
LIST.xlsm | |||||||||
---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | |||
1 | DATE | INVOICE NO | CODE | BRAND | QTY | UNIT PRICE | TOTAL | ||
2 | 2024.01.26 | 445 | 1306 | BS 750R16 R230 JAP | 2 | 775 | 1550 | ||
3 | DATE | INVOICE NO | CODE | BRAND | QTY | UNIT PRICE | TOTAL | ||
4 | 2024.01.26 | 446 | 1306 | BS 750R16 R230 JAP | 1 | 780 | 780 | ||
5 | DATE | INVOICE NO | CODE | BRAND | QTY | UNIT PRICE | TOTAL | ||
6 | 2024.01.26 | 447 | 1306 | BS 750R16 R230 JAP | 1 | 780 | 1550 | ||
7 | 1305 | BS 700R16 R230 JAP | 1 | 770 | |||||
8 | MOVEMENT : SELLING RETURNS | ||||||||
9 | DATE | INVOICE NO | CODE | BRAND | QTY | UNIT PRICE | TOTAL | ||
10 | 2024.01.24 | 330 | 1306 | BS 750R16 R230 JAP | 2 | 775 | 1550 | ||
11 | DATE | INVOICE NO | CODE | BRAND | QTY | UNIT PRICE | TOTAL | ||
12 | 2024.01.24 | 331 | 1306 | BS 750R16 R230 JAP | 1 | 780 | 780 | ||
13 | DATE | INVOICE NO | CODE | BRAND | QTY | UNIT PRICE | TOTAL | ||
14 | 2024.01.24 | 332 | 1305 | BS 700R16 R230 JAP | 1 | 780 | 1560 | ||
15 | 1306 | BS 750R16 R230 JAP | 1 | 780 | |||||
PAGE 2 |
LIST.xlsm | |||||||||
---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | |||
1 | DATE | INVOICE NO | CODE | BRAND | QTY | UNIT PRICE | TOTAL | ||
2 | 2024.01.25 | 333 | 1227 | BS 215/70R15C R623 THI | 1 | 425 | 1160 | ||
3 | 1287 | GC 315/80R22.5 AZ126 CHI | 1 | 735 | |||||
4 | DATE | INVOICE NO | CODE | BRAND | QTY | UNIT PRICE | TOTAL | ||
5 | 2024.01.25 | 334 | 1287 | GC 315/80R22.5 AZ126 CHI | 1 | 735 | 735 | ||
6 | MOVEMENT : PURCHASING RETURNS | ||||||||
7 | DATE | INVOICE NO | CODE | BRAND | QTY | UNIT PRICE | TOTAL | ||
8 | 2024.01.22 | 220 | 1284 | GC 1200R20 AZ0026 CHI | 1 | 895 | 18925 | ||
9 | 1285 | GC 1200R20 AZ0183 CHI | 2 | 925 | |||||
10 | 1385 | GC 315/80R22.5 AT161 CHI | 2 | 735 | |||||
11 | 1287 | GC 315/80R22.5 AZ126 CHI | 2 | 735 | |||||
12 | DATE | INVOICE NO | CODE | BRAND | QTY | UNIT PRICE | TOTAL | ||
13 | 2024.01.22 | 221 | 1241 | BS 1200R20 G580 JAP | 2 | 2035 | 4920 | ||
14 | 1227 | BS 215/70R15C R623 THI | 2 | 425 | |||||
PAGE 3 |
LIST.xlsm | |||||||||
---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | |||
1 | DATE | INVOICE NO | CODE | BRAND | QTY | UNIT PRICE | TOTAL | ||
2 | 2024.01.22 | 222 | 1284 | GC 1200R20 AZ0026 CHI | 1 | 895 | 1820 | ||
3 | 1285 | GC 1200R20 AZ0183 CHI | 1 | 925 | |||||
4 | DATE | INVOICE NO | CODE | BRAND | QTY | UNIT PRICE | TOTAL | ||
5 | 2024.01.23 | 223 | 1241 | BS 1200R20 G580 JAP | 2 | 2035 | 4920 | ||
PAGE 4 |
LIST.xlsm | ||||||
---|---|---|---|---|---|---|
A | B | C | D | |||
1 | DATE | DESCIBE | DEBIT | CREDIT | ||
2 | 2024.01.10 | RECEIVED VBGHH200 | 2,000.00 | |||
3 | 2024.01.10 | PAID HJU8888 | 2,000.00 | |||
4 | 2024.01.11 | RECEIVED VBGHH201 | 1,500.00 | |||
5 | 2024.01.12 | PAID HJU8889 | 100.00 | |||
6 | 2024.01.13 | PAID HJU8890 | 200 | |||
7 | 2024.01.14 | PAID HJU8891 | 225 | |||
8 | 2024.01.15 | RECEIVED VBGHH202 | 30000 | |||
9 | 2024.01.16 | RECEIVED VBGHH203 | 40000 | |||
10 | ||||||
11 | ||||||
12 | ||||||
13 | ||||||
14 | ||||||
15 | ||||||
ABS |
RESULT SHOULD BE IN LIST sheet
LIST.xlsm | |||||||
---|---|---|---|---|---|---|---|
A | B | C | D | E | |||
1 | DATE | DESCRIBE | DEBIT | CREDIT | BALANCE | ||
2 | 31/12/2023 | BALANCE | - | -250,000.00 | -250,000.00 | ||
3 | 2024.01.02 | PURCHASING | - | 242,560.00 | -492,560.00 | ||
4 | 2024.01.09 | PURCHASING | - | 10,290.00 | -502,850.00 | ||
5 | 2024.01.10 | PURCHASING | - | 850.00 | -503,700.00 | ||
6 | 2024.01.10 | RECEIVED | - | 2,000.00 | -505,700.00 | ||
7 | 2024.01.10 | PAID | 2,000.00 | - | -503,700.00 | ||
8 | 2024.01.11 | RECEIVED | - | 1,500.00 | -505,200.00 | ||
9 | 2024.01.12 | PAID | 100.00 | - | -505,100.00 | ||
10 | 2024.01.13 | PAID | 200.00 | - | -504,900.00 | ||
11 | 2024.01.14 | PAID | 225.00 | - | -504,675.00 | ||
12 | 2024.04.14 | PURCHASING | - | 81,950.00 | -586,625.00 | ||
13 | 2024.01.15 | RECEIVED | - | 30,000.00 | -616,625.00 | ||
14 | 2024.01.16 | RECEIVED | - | 40,000.00 | -656,625.00 | ||
15 | 2024.04.17 | PURCHASING | - | 46,600.00 | -703,225.00 | ||
16 | 2024.04.17 | PURCHASING | - | 735.00 | -703,960.00 | ||
17 | 2024.04.18 | PURCHASING | - | 4,250.00 | -708,210.00 | ||
18 | 2024.01.20 | PURCHASING | - | 19,110.00 | -727,320.00 | ||
19 | 2024.01.20 | PURCHASING | - | 391,650.00 | -1,118,970.00 | ||
20 | 2024.01.22 | PURCHASING RETURNS | - | 18,925.00 | -1,137,895.00 | ||
21 | 2024.01.22 | PURCHASING RETURNS | - | 4,920.00 | -1,142,815.00 | ||
22 | 2024.01.22 | PURCHASING RETURNS | - | 1,820.00 | -1,144,635.00 | ||
23 | 2024.01.23 | PURCHASING RETURNS | - | 4,920.00 | -1,149,555.00 | ||
24 | 2024.01.24 | SELLING RETURNS | - | 1,550.00 | -1,151,105.00 | ||
25 | 2024.01.24 | SELLING RETURNS | - | 780.00 | -1,151,885.00 | ||
26 | 2024.01.24 | SELLING RETURNS | - | 1,560.00 | -1,153,445.00 | ||
27 | 2024.01.24 | SELLING | 7,750.00 | - | -1,145,695.00 | ||
28 | 2024.01.24 | SELLING | 1,560.00 | - | -1,144,135.00 | ||
29 | 2024.01.24 | SELLING | 2,285.00 | - | -1,141,850.00 | ||
30 | 2024.01.25 | SELLING RETURNS | - | 1,160.00 | -1,143,010.00 | ||
31 | 2024.01.25 | SELLING RETURNS | - | 735.00 | -1,143,745.00 | ||
32 | 2024.01.25 | SELLING | 850.00 | - | -1,142,895.00 | ||
33 | 2024.01.25 | SELLING | 735.00 | - | -1,142,160.00 | ||
34 | 2024.01.26 | SELLING | 1,550.00 | - | -1,140,610.00 | ||
35 | 2024.01.26 | SELLING | 1,550.00 | - | -1,139,060.00 | ||
36 | 2024.03.30 | PURCHASING | - | 12,250.00 | -1,151,310.00 | ||
37 | 2024.03.30 | PURCHASING | - | 2,176.00 | -1,153,486.00 | ||
LIST |
Cell Formulas | ||
---|---|---|
Range | Formula | |
E3:E37 | E3 | =E2+C3-D3 |