Hi
I have information in INVOICE sheet and VOUCHER sheet should create report in CUSTOMER sheet based on coiditions
so when fill details in INVOICE sheet
CUSTOMER sheet
result based on C2 in INVOICE sheet when the word is SALES will put amount is existed in last row in column D for customer sheet and bring the date from F2 and the name from D5 and brings the C2 & F6:F7 as I did in column C for customer sheet and calculate in column F for the first row like this
when write PURCHASE in C2
result based on C2 in INVOICE sheet when the word is PURCHASE will put amount is existed in last row in column E for CUSTOMER sheet and bring the date from F2 and the name from D5 and brings the C2 & F6:F7 as I did in column C for customer sheet like this and calculate in column F for the name
when write PURCHASE RETURNS in C2
Result based on C2 in INVOICE sheet when the word is PURCHASE RETURNS will put amount is existed in last row in column D for CUSTOMER sheet and bring the date from F2 and the name from D5 and brings the C2 & F6:F7 as I did in column C for customer sheet And calculate in column F for the name like this
when write SALES RETURNS in C2
Result based on C2 in INVOICE sheet when the word is SALES RETURNS will put amount is existed in last row in column E for CUSTOMER sheet and bring the date from F2 and the name from D5 and brings the C2 & F6:F7 as I did in column C for customer sheet And calculate in column F for the name like this
and voucher sheet when fill in A7 RECEIPT word
Result based on A7 in VOUCHER NO sheet when the word is RECEIPT will put amount is existed A3 to column D for CUSTOMER sheet and bring the date from D2 and the name from B5 and brings the C7 & D6:D7 as I did in column C for customer sheet And calculate in column F for the name like this
and voucher sheet when fill in A7 PAYING word
Result based on A7 in VOUCHER NO sheet when the word is RECEIPT will put amount is existed in A3 to column E for CUSTOMER sheet and bring the date from D2 and the name from B5 and brings the C7 & D6:D7 as I did in column C for customer sheet And calculate in column F for the name like this
last thing if I have another name then will contains two different formulas as I did for OMAR and if I repeat the name will sort under duplicates the same name .
I hope there is no error no missed data , if it's just informe me
thanks in advance .
I have information in INVOICE sheet and VOUCHER sheet should create report in CUSTOMER sheet based on coiditions
so when fill details in INVOICE sheet
ورقة عمل Microsoft Excel جديد.xlsx | |||||||
---|---|---|---|---|---|---|---|
B | C | D | E | F | |||
1 | TYPE INVOICE | DATE | |||||
2 | SALES | 11/01/2024 | |||||
3 | |||||||
4 | NAME | ||||||
5 | OMAR | ||||||
6 | INVOICE NO | ||||||
7 | NN 12220000122 | ||||||
8 | |||||||
9 | |||||||
10 | |||||||
11 | ITEM | ID | QTY | UNIT PRICE | BALANCE | ||
12 | 1 | VGFHJJ-001 | 700.00 | 120.00 | 84,000.00 | ||
13 | 2 | VGFHJJ-002 | 200.00 | 130.00 | 26,000.00 | ||
14 | 3 | VGFHJJ-003 | 120.00 | 100.00 | 12,000.00 | ||
15 | 4 | VGFHJJ-004 | 23.00 | 220.00 | 5,060.00 | ||
16 | |||||||
17 | |||||||
18 | |||||||
19 | |||||||
20 | SUM | 127,060.00 | |||||
INVOICE NO |
Cell Formulas | ||
---|---|---|
Range | Formula | |
F2 | F2 | =TODAY() |
F12:F15 | F12 | =D12*E12 |
F20 | F20 | =SUM(F12:F15) |
CUSTOMER sheet
ورقة عمل Microsoft Excel جديد.xlsx | ||||||||
---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | |||
1 | DATE | CUSTOMER | DETAILS | DEBIT | CREDIT | BALANCE | ||
2 | ||||||||
3 | ||||||||
4 | ||||||||
5 | ||||||||
6 | ||||||||
7 | ||||||||
8 | ||||||||
9 | ||||||||
10 | ||||||||
CUSTOMER |
result based on C2 in INVOICE sheet when the word is SALES will put amount is existed in last row in column D for customer sheet and bring the date from F2 and the name from D5 and brings the C2 & F6:F7 as I did in column C for customer sheet and calculate in column F for the first row like this
ورقة عمل Microsoft Excel جديد.xlsx | ||||||||
---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | |||
1 | DATE | CUSTOMER | DETAILS | DEBIT | CREDIT | BALANCE | ||
2 | 11/01/2024 | OMAR | SALES INVOICE NO NN 12220000122 | 127,060.00 | 127,060.00 | |||
3 | ||||||||
4 | ||||||||
5 | ||||||||
6 | ||||||||
7 | ||||||||
8 | ||||||||
9 | ||||||||
10 | ||||||||
CUSTOMER |
Cell Formulas | ||
---|---|---|
Range | Formula | |
F2 | F2 | =D2-E2 |
when write PURCHASE in C2
ورقة عمل Microsoft Excel جديد.xlsx | |||||||
---|---|---|---|---|---|---|---|
B | C | D | E | F | |||
1 | TYPE INVOICE | DATE | |||||
2 | PURCHASE | 11/01/2024 | |||||
3 | |||||||
4 | NAME | ||||||
5 | OMAR | ||||||
6 | INVOICE NO | ||||||
7 | PP 12220000199 | ||||||
8 | |||||||
9 | |||||||
10 | |||||||
11 | ITEM | ID | QTY | UNIT PRICE | BALANCE | ||
12 | 1 | VGFHJJ-001 | 700.00 | 120.00 | 84,000.00 | ||
13 | 2 | VGFHJJ-002 | 200.00 | 130.00 | 26,000.00 | ||
14 | 3 | VGFHJJ-003 | 120.00 | 100.00 | 12,000.00 | ||
15 | 4 | VGFHJJ-004 | 23.00 | 220.00 | 5,060.00 | ||
16 | |||||||
17 | |||||||
18 | |||||||
19 | |||||||
20 | SUM | 127,060.00 | |||||
INVOICE NO |
Cell Formulas | ||
---|---|---|
Range | Formula | |
F2 | F2 | =TODAY() |
F12:F15 | F12 | =D12*E12 |
F20 | F20 | =SUM(F12:F15) |
result based on C2 in INVOICE sheet when the word is PURCHASE will put amount is existed in last row in column E for CUSTOMER sheet and bring the date from F2 and the name from D5 and brings the C2 & F6:F7 as I did in column C for customer sheet like this and calculate in column F for the name
ورقة عمل Microsoft Excel جديد.xlsx | ||||||||
---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | |||
1 | DATE | CUSTOMER | DETAILS | DEBIT | CREDIT | BALANCE | ||
2 | 11/01/2024 | OMAR | SALES INVOICE NO NN 12220000122 | 127,060.00 | 127,060.00 | |||
3 | 11/01/2024 | OMAR | PURCHASE INVOICE NO PP 12220000199 | 127,060.00 | 0.00 | |||
CUSTOMER |
Cell Formulas | ||
---|---|---|
Range | Formula | |
F2 | F2 | =D2-E2 |
F3 | F3 | =F2+D3-E3 |
when write PURCHASE RETURNS in C2
ورقة عمل Microsoft Excel جديد.xlsx | |||||||
---|---|---|---|---|---|---|---|
B | C | D | E | F | |||
1 | TYPE INVOICE | DATE | |||||
2 | PURCHASE RETURNS | 11/01/2024 | |||||
3 | |||||||
4 | NAME | ||||||
5 | OMAR | ||||||
6 | INVOICE NO | ||||||
7 | PU 2330000188 | ||||||
8 | |||||||
9 | |||||||
10 | |||||||
11 | ITEM | ID | QTY | UNIT PRICE | BALANCE | ||
12 | 1 | VGFHJJ-001 | 700.00 | 120.00 | 84,000.00 | ||
13 | 2 | VGFHJJ-002 | 200.00 | 130.00 | 26,000.00 | ||
14 | 3 | VGFHJJ-003 | 120.00 | 100.00 | 12,000.00 | ||
15 | 4 | VGFHJJ-004 | 23.00 | 220.00 | 5,060.00 | ||
16 | |||||||
17 | |||||||
18 | |||||||
19 | |||||||
20 | SUM | 127,060.00 | |||||
INVOICE NO |
Cell Formulas | ||
---|---|---|
Range | Formula | |
F2 | F2 | =TODAY() |
F12:F15 | F12 | =D12*E12 |
F20 | F20 | =SUM(F12:F15) |
Result based on C2 in INVOICE sheet when the word is PURCHASE RETURNS will put amount is existed in last row in column D for CUSTOMER sheet and bring the date from F2 and the name from D5 and brings the C2 & F6:F7 as I did in column C for customer sheet And calculate in column F for the name like this
ورقة عمل Microsoft Excel جديد.xlsx | ||||||||
---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | |||
1 | DATE | CUSTOMER | DETAILS | DEBIT | CREDIT | BALANCE | ||
2 | 11/01/2024 | OMAR | SALES INVOICE NO NN 12220000122 | 127,060.00 | 127,060.00 | |||
3 | 11/01/2024 | OMAR | PURCHASE INVOICE NO PP 12220000199 | 127,060.00 | 0.00 | |||
4 | 11/01/2024 | OMAR | PURCHASE RETURNS INVOICE NO PU 2330000188 | 127,060.00 | 127,060.00 | |||
CUSTOMER |
Cell Formulas | ||
---|---|---|
Range | Formula | |
F2 | F2 | =D2-E2 |
F3:F4 | F3 | =F2+D3-E3 |
when write SALES RETURNS in C2
ورقة عمل Microsoft Excel جديد.xlsx | |||||||
---|---|---|---|---|---|---|---|
B | C | D | E | F | |||
1 | TYPE INVOICE | DATE | |||||
2 | SALES RETURNS | 11/01/2024 | |||||
3 | |||||||
4 | NAME | ||||||
5 | OMAR | ||||||
6 | INVOICE NO | ||||||
7 | SSE 7688-900 | ||||||
8 | |||||||
9 | |||||||
10 | |||||||
11 | ITEM | ID | QTY | UNIT PRICE | BALANCE | ||
12 | 1 | VGFHJJ-001 | 700.00 | 120.00 | 84,000.00 | ||
13 | 2 | VGFHJJ-002 | 200.00 | 130.00 | 26,000.00 | ||
14 | 3 | VGFHJJ-003 | 120.00 | 100.00 | 12,000.00 | ||
15 | 4 | VGFHJJ-004 | 23.00 | 220.00 | 5,060.00 | ||
16 | |||||||
17 | |||||||
18 | |||||||
19 | |||||||
20 | SUM | 127,060.00 | |||||
INVOICE NO |
Cell Formulas | ||
---|---|---|
Range | Formula | |
F2 | F2 | =TODAY() |
F12:F15 | F12 | =D12*E12 |
F20 | F20 | =SUM(F12:F15) |
Result based on C2 in INVOICE sheet when the word is SALES RETURNS will put amount is existed in last row in column E for CUSTOMER sheet and bring the date from F2 and the name from D5 and brings the C2 & F6:F7 as I did in column C for customer sheet And calculate in column F for the name like this
ورقة عمل Microsoft Excel جديد.xlsx | ||||||||
---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | |||
1 | DATE | CUSTOMER | DETAILS | DEBIT | CREDIT | BALANCE | ||
2 | 11/01/2024 | OMAR | SALES INVOICE NO NN 12220000122 | 127,060.00 | 127,060.00 | |||
3 | 11/01/2024 | OMAR | PURCHASE INVOICE NO PP 12220000199 | 127,060.00 | 0.00 | |||
4 | 11/01/2024 | OMAR | PURCHASE RETURNS INVOICE NO PU 2330000188 | 127,060.00 | 127,060.00 | |||
5 | 11/01/2024 | OMAR | SALES RETURNS INVOICE NO SSE 7688-900 | 127,060.00 | 0.00 | |||
6 | ||||||||
7 | ||||||||
8 | ||||||||
9 | ||||||||
10 | ||||||||
CUSTOMER |
Cell Formulas | ||
---|---|---|
Range | Formula | |
F2 | F2 | =D2-E2 |
F3:F5 | F3 | =F2+D3-E3 |
and voucher sheet when fill in A7 RECEIPT word
ورقة عمل Microsoft Excel جديد.xlsx | ||||||
---|---|---|---|---|---|---|
A | B | C | D | |||
1 | DATE | |||||
2 | AMOUNT | 11/01/2024 | ||||
3 | 10,000.00 | |||||
4 | NAME | |||||
5 | OMAR | |||||
6 | CASE | VOUCHER NO | ||||
7 | RECEIPT | VVR NO 1233333 | ||||
VOUCHER NO |
Cell Formulas | ||
---|---|---|
Range | Formula | |
D2 | D2 | =TODAY() |
Result based on A7 in VOUCHER NO sheet when the word is RECEIPT will put amount is existed A3 to column D for CUSTOMER sheet and bring the date from D2 and the name from B5 and brings the C7 & D6:D7 as I did in column C for customer sheet And calculate in column F for the name like this
ورقة عمل Microsoft Excel جديد.xlsx | ||||||||
---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | |||
1 | DATE | CUSTOMER | DETAILS | DEBIT | CREDIT | BALANCE | ||
2 | 11/01/2024 | OMAR | SALES INVOICE NO NN 12220000122 | 127,060.00 | 127,060.00 | |||
3 | 11/01/2024 | OMAR | PURCHASE INVOICE NO PP 12220000199 | 127,060.00 | 0.00 | |||
4 | 11/01/2024 | OMAR | PURCHASE RETURNS INVOICE NO PU 2330000188 | 127,060.00 | 127,060.00 | |||
5 | 11/01/2024 | OMAR | SALES RETURNS INVOICE NO SSE 7688-900 | 127,060.00 | 0.00 | |||
6 | 11/01/2024 | OMAR | RECEIPT VOUCHER NO VVR NO 1233333 | 10,000.00 | 10,000.00 | |||
CUSTOMER |
Cell Formulas | ||
---|---|---|
Range | Formula | |
F2 | F2 | =D2-E2 |
F3:F6 | F3 | =F2+D3-E3 |
and voucher sheet when fill in A7 PAYING word
ورقة عمل Microsoft Excel جديد.xlsx | ||||||
---|---|---|---|---|---|---|
A | B | C | D | |||
1 | DATE | |||||
2 | AMOUNT | 11/01/2024 | ||||
3 | 3,000.00 | |||||
4 | NAME | |||||
5 | OMAR | |||||
6 | CASE | VOUCHER NO | ||||
7 | PAYING | VVP NO 8999_990 | ||||
VOUCHER NO |
Result based on A7 in VOUCHER NO sheet when the word is RECEIPT will put amount is existed in A3 to column E for CUSTOMER sheet and bring the date from D2 and the name from B5 and brings the C7 & D6:D7 as I did in column C for customer sheet And calculate in column F for the name like this
ورقة عمل Microsoft Excel جديد.xlsx | ||||||||
---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | |||
1 | DATE | CUSTOMER | DETAILS | DEBIT | CREDIT | BALANCE | ||
2 | 11/01/2024 | OMAR | SALES INVOICE NO NN 12220000122 | 127,060.00 | 127,060.00 | |||
3 | 11/01/2024 | OMAR | PURCHASE INVOICE NO PP 12220000199 | 127,060.00 | 0.00 | |||
4 | 11/01/2024 | OMAR | PURCHASE RETURNS INVOICE NO PU 2330000188 | 127,060.00 | 127,060.00 | |||
5 | 11/01/2024 | OMAR | SALES RETURNS INVOICE NO SSE 7688-900 | 127,060.00 | 0.00 | |||
6 | 11/01/2024 | OMAR | RECEIPT VOUCHER NO VVR NO 1233333 | 10,000.00 | 10,000.00 | |||
7 | 11/01/2024 | OMAR | PAID VOUCHER NO VVP NO 8999_990 | 3,000.00 | 7,000.00 | |||
CUSTOMER |
Cell Formulas | ||
---|---|---|
Range | Formula | |
F2 | F2 | =D2-E2 |
F3:F7 | F3 | =F2+D3-E3 |
last thing if I have another name then will contains two different formulas as I did for OMAR and if I repeat the name will sort under duplicates the same name .
I hope there is no error no missed data , if it's just informe me
thanks in advance .