abdelfattah
Well-known Member
- Joined
- May 3, 2019
- Messages
- 1,507
- Office Version
- 2019
- 2010
- Platform
- Windows
hello
i need help from experts . what I want add a new rows based on two cells L2,M2 .L2=NAME , M2= ROWS NUMBERS so if I write the name and the rows numbers . it should look at to COL C to match the name and insert the rows based on L2,M2 , and if the L2,M2 are empty then should delete all of empty rows for all names and if the L2= empty and the M2 is rows numbers then insert rows for all names .
note: it should insert row with the same borders and formulas
this the orginal data
i need help from experts . what I want add a new rows based on two cells L2,M2 .L2=NAME , M2= ROWS NUMBERS so if I write the name and the rows numbers . it should look at to COL C to match the name and insert the rows based on L2,M2 , and if the L2,M2 are empty then should delete all of empty rows for all names and if the L2= empty and the M2 is rows numbers then insert rows for all names .
note: it should insert row with the same borders and formulas
this the orginal data
MATCH.xlsm | |||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | |||
1 | NAME | DEBIT | CREDIT | BALANCE | name | row | |||||||||||
2 | 1,000.00 | 200.00 | 800.00 | ||||||||||||||
3 | ITEM | DATE | NAME | INVOICE NO | NOTE | WAY OF PAID | DEBIT | CREDIT | BALANCE | ||||||||
4 | 1 | 8/7/2021 | ALI1 | INV-1000 | NO PAID | - | 1000 | 1,000.00 | |||||||||
5 | 2 | 8/10/2021 | ALI1 | INV-1000 | PAID | CASH | 200.00 | 800.00 | |||||||||
6 | |||||||||||||||||
7 | |||||||||||||||||
8 | NAME | DEBIT | CREDIT | BALANCE | |||||||||||||
9 | 3,200.00 | 100.00 | 3,100.00 | ||||||||||||||
10 | ITEM | DATE | NAME | INVOICE NO | NOTE | WAY OF PAID | DEBIT | CREDIT | BALANCE | ||||||||
11 | 1 | 8/8/2021 | ALI2 | INV-1001 | NO PAID | - | 2000 | 2,000.00 | |||||||||
12 | 2 | 8/11/2021 | ALI2 | INV-1001 | PAID | CASH | 100.00 | 1,900.00 | |||||||||
13 | 3 | 8/13/2021 | ALI2 | INV-1003 | NO PAID | - | 1200 | 3,100.00 | |||||||||
14 | |||||||||||||||||
15 | NAME | DEBIT | CREDIT | BALANCE | |||||||||||||
16 | 3,800.00 | 1,000.00 | 2,800.00 | ||||||||||||||
17 | ITEM | DATE | NAME | INVOICE NO | NOTE | WAY OF PAID | DEBIT | CREDIT | BALANCE | ||||||||
18 | 1 | 7/16/2021 | ALI3 | INV-1002 | PAID | CASH | 3000 | 500.00 | 2,500.00 | ||||||||
19 | 2 | 7/16/2021 | ALI3 | INV-1002 | PAID | BANK | 500.00 | 2,000.00 | |||||||||
20 | 3 | 7/16/2021 | ALI3 | INV-1004 | NO PAID | - | 800 | 2,800.00 | |||||||||
result |
Cell Formulas | ||
---|---|---|
Range | Formula | |
B2:C2 | B2 | =SUM(G4:G5) |
D2,I18,D16,I11,D9,I4 | D2 | =B2-C2 |
I5,I19:I20,I12:I13 | I5 | =I4+G5-H5 |
B16:C16,B9:C9 | B9 | =SUM(G11:G13) |