Hi
the result should be in G2,G3 for BALANCES sheet.
in column (RECEIVED/PAID/NOT PAY) as in header for each sheet contains BANK or CASH should merge amounts are existed in last column with exclude AGGREGATE row from calculation across sheets first .
second should subtract the merged amounts from each other of them (RECEIVED -PAID) for BANK or CASH .
after that should sum the balance to B2,B3
example:
when merge RECEIVED for BANK for three sheets(SL,RS,VC)
RECEIVED BANK=40400+13400+480+120+200=54600
when merge PAID for BANK for four sheets(SL,RS,EP,VC)
PAID BANK=15000+2620+1100+1100+11500+1500+200+200=33200
after that should be 54600-33200=21380
final step when show the result in G2 for BANK should add to B2 will be like this=
21380+200000=221380 as show in G2
the same thing for CAH whether PAID or RECEIVED.
any suggestion to do that by formula or vba will be great.
before
after
the result should be in G2,G3 for BALANCES sheet.
in column (RECEIVED/PAID/NOT PAY) as in header for each sheet contains BANK or CASH should merge amounts are existed in last column with exclude AGGREGATE row from calculation across sheets first .
second should subtract the merged amounts from each other of them (RECEIVED -PAID) for BANK or CASH .
after that should sum the balance to B2,B3
example:
when merge RECEIVED for BANK for three sheets(SL,RS,VC)
RECEIVED BANK=40400+13400+480+120+200=54600
when merge PAID for BANK for four sheets(SL,RS,EP,VC)
PAID BANK=15000+2620+1100+1100+11500+1500+200+200=33200
after that should be 54600-33200=21380
final step when show the result in G2 for BANK should add to B2 will be like this=
21380+200000=221380 as show in G2
the same thing for CAH whether PAID or RECEIVED.
any suggestion to do that by formula or vba will be great.
| BANK.xlsm | ||||||||||
|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | |||
| 1 | ITEM | DATE | CLIENT | ID | RECEIVED/PAID/NOT PAY | QTY | UNIT PRICE | TOTAL | ||
| 2 | 1 | 01/01/2024 | OMRAN | FSLOO90 | RECEIVED CASH RE2000 | 200.00 | 100.00 | 20,000.00 | ||
| 3 | 2 | 01/01/2024 | OMRAN | SDK-100 | RECEIVED CASH RE2000 | 20.00 | 110.00 | 2,200.00 | ||
| 4 | AGGREGATE | 22,200.00 | ||||||||
| 5 | 1 | 01/01/2024 | OMAAR | TTR-1000 | NOT PAY | 22.00 | 120.00 | 2,640.00 | ||
| 6 | AGGREGATE | 2,640.00 | ||||||||
| 7 | 1 | 02/01/2024 | AMIR | TRE400-90 | RECEIVED BANK SWIFT 234441222 | 120.00 | 110.00 | 13,200.00 | ||
| 8 | 2 | 02/01/2024 | AMIR | TRE400-91 | RECEIVED BANK SWIFT 234441222 | 12.00 | 130.00 | 1,560.00 | ||
| 9 | 3 | 02/01/2024 | AMIR | TRE400-92 | RECEIVED BANK SWIFT 234441222 | 122.00 | 120.00 | 14,640.00 | ||
| 10 | 4 | 02/01/2024 | AMIR | TRE400-93 | RECEIVED BANK SWIFT 234441222 | 100.00 | 110.00 | 11,000.00 | ||
| 11 | AGGREGATE | 40,400.00 | ||||||||
| 12 | 1 | 02/01/2024 | AMIR | TTR-1000 | RECEIVED BANK CHEQUE 122 | 20.00 | 120.00 | 2,400.00 | ||
| 13 | 2 | 02/01/2024 | AMIR | TTR-1001 | RECEIVED BANK CHEQUE 122 | 100.00 | 110.00 | 11,000.00 | ||
| 14 | AGGREGATE | 13,400.00 | ||||||||
SL | ||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| H12:H13,H7:H10,H5,H2:H3 | H2 | =F2*G2 |
| H4,H14 | H4 | =SUM(H2:H3) |
| H6 | H6 | =H5 |
| H11 | H11 | =SUM(H7:H10) |
| BANK.xlsm | ||||||||||
|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | |||
| 1 | ITEM | DATE | CLIENT | ID | RECEIVED/PAID/NOT PAY | QTY | UNIT PRICE | TOTAL | ||
| 2 | 1 | 01/01/2024 | MUH | FSLOO90 | PAID CASH AS2000 | 10.00 | 110.00 | 1,100.00 | ||
| 3 | AGGREGATE | 1,100.00 | ||||||||
| 4 | 1 | 01/01/2024 | NURI | FSLOO90 | NOT PAY | 22.00 | 110.00 | 2,420.00 | ||
| 5 | AGGREGATE | 2,420.00 | ||||||||
| 6 | 1 | 02/01/2024 | ANUR | FSLOO90 | PAID BANK SWIFT 234441288 | 120.00 | 110.00 | 13,200.00 | ||
| 7 | 2 | 02/01/2024 | ANUR | TRE400-91 | PAID BANK SWIFT 234441288 | 12.00 | 150.00 | 1,800.00 | ||
| 8 | AGGREGATE | 15,000.00 | ||||||||
| 9 | 1 | 02/01/2024 | ANUR | FSLOO90 | PAID BANK CHEQUE 12211 | 20.00 | 120.00 | 2,400.00 | ||
| 10 | 2 | 02/01/2024 | ANUR | TTR-1001 | PAID BANK CHEQUE 12211 | 10.00 | 22.00 | 220.00 | ||
| 11 | AGGREGATE | 2,620.00 | ||||||||
| 12 | 1 | 02/01/2024 | ANUR | FSLOO90 | PAID CASH AS200011 | 20.00 | 120.00 | 2,400.00 | ||
| 13 | AGGREGATE | 2,400.00 | ||||||||
| 14 | 1 | 02/01/2024 | NURI | FSLOO90 | NOT PAY | 2.00 | 110.00 | 220.00 | ||
| 15 | AGGREGATE | 220.00 | ||||||||
PR | ||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| H2,H14,H12,H9:H10,H6:H7,H4 | H2 | =F2*G2 |
| H3,H13 | H3 | =SUM(H2:H2) |
| H5,H15 | H5 | =H4 |
| H8,H11 | H8 | =SUM(H6:H7) |
| BANK.xlsm | ||||||||||
|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | |||
| 1 | ITEM | DATE | CLIENT | ID | RECEIVED/PAID/NOT PAY | QTY | UNIT PRICE | TOTAL | ||
| 2 | 1 | 02/01/2024 | ANUR | FSLOO90 | NOT PAY | 1.00 | 120.00 | 120.00 | ||
| 3 | 2 | 02/01/2024 | ANUR | TTR-1001 | NOT PAY | 1.00 | 22.00 | 22.00 | ||
| 4 | AGGREGATE | 142.00 | ||||||||
| 5 | 1 | 02/01/2024 | OMRAN | TTR-1001 | PAID BANK SWIFT 25555 | 10.00 | 110.00 | 1,100.00 | ||
| 6 | AGGREGATE | 1,100.00 | ||||||||
| 7 | 1 | 02/01/2024 | ALI | TTR-1001 | PAID BANK CHEQUE 122222 | 10.00 | 110.00 | 1,100.00 | ||
| 8 | AGGREGATE | 1,100.00 | ||||||||
| 9 | 1 | 02/01/2024 | ALI | FSLOO90 | PAID CASH RE 1222212 | 10.00 | 110.00 | 1,100.00 | ||
| 10 | AGGREGATE | 1,100.00 | ||||||||
RP | ||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| H9,H7,H5,H2:H3 | H2 | =F2*G2 |
| H4 | H4 | =SUM(H2:H3) |
| H6,H10,H8 | H6 | =SUM(H5) |
| BANK.xlsm | ||||||||||
|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | |||
| 1 | ITEM | DATE | CLIENT | EX | RECEIVED/PAID/NOT PAY | QTY | UNIT PRICE | TOTAL | ||
| 2 | 1 | 02/01/2024 | NN | LABOR | PAID BANK 123333 | 1.00 | 1,500.00 | 1,500.00 | ||
| 3 | 2 | 02/01/2024 | NN | HIRE | PAID BANK 123333 | 2.00 | 5,000.00 | 10,000.00 | ||
| 4 | AGGREGATE | 11,500.00 | ||||||||
| 5 | 1 | 02/01/2024 | MMM | SERVICE | PAID BANK 123334 | 1.00 | 1,500.00 | 1,500.00 | ||
| 6 | AGGREGATE | 1,500.00 | ||||||||
| 7 | 1 | 02/01/2024 | SS | SALARY ESS | PAID CASH RE200011 | 1.00 | 2,000.00 | 2,000.00 | ||
| 8 | 2 | 02/01/2024 | SS | SALARY ESD | PAID CASH RE200011 | 1.00 | 3,000.00 | 3,000.00 | ||
| 9 | AGGREGATE | 5,000.00 | ||||||||
| 10 | 1 | 02/01/2024 | TT | LABOR | PAID CASH RE200014 | 2.00 | 2,000.00 | 4,000.00 | ||
| 11 | AGGREGATE | 4,000.00 | ||||||||
EP | ||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| H10,H7:H8,H5,H2:H3 | H2 | =F2*G2 |
| H4,H9 | H4 | =SUM(H2:H3) |
| H6 | H6 | =SUM(H5) |
| H11 | H11 | =SUM(H10:H10) |
| BANK.xlsm | |||||||
|---|---|---|---|---|---|---|---|
| A | B | C | D | E | |||
| 1 | ITEM | DATE | CLIENT | RECEIVED/PAID/NOT PAY | TOTAL | ||
| 2 | 1 | 02/01/2024 | OMAAR | PAID BANK 4444 | 200.00 | ||
| 3 | 2 | 02/01/2024 | NURI | PAID BANK 4445 | 200.00 | ||
| 4 | 3 | 02/01/2024 | NURI | PAID CASH RE3001 | 110.00 | ||
| 5 | 4 | 02/01/2024 | AMIR | PAID CASH RE3002 | 100.00 | ||
| 6 | 5 | 02/01/2024 | SS | RECEIVED CASH RE400011 | 100.00 | ||
| 7 | 6 | 03/01/2024 | SS | RECEIVED CASH RE400012 | 110.00 | ||
| 8 | 7 | 04/01/2024 | SS | RECEIVED BANK SWIFT RE400013 | 120.00 | ||
| 9 | 8 | 05/01/2024 | SS | RECEIVED BANK CHEQUE CR1000 | 200.00 | ||
VC | |||||||
before
| BANK.xlsm | |||||||||
|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | |||
| 1 | FIRST DURETION | BALANCES | ACCOUNTS | BALANCES | |||||
| 2 | BANK | 200,000.00 | BANK | ||||||
| 3 | CASH | 300,000.00 | CASH | ||||||
BALANCES | |||||||||
after
| BANK.xlsm | |||||||||
|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | |||
| 1 | FIRST DURETION | BALANCES | ACCOUNTS | BALANCES | |||||
| 2 | BANK | 200,000.00 | BANK | 221,380.00 | |||||
| 3 | CASH | 300,000.00 | CASH | 311,200.00 | |||||
BALANCES | |||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| G2:G3 | G2 | =B2+SL!L2-PR!K2 |
