Too much condition to pull information invoice from multiple sheets

O

Omar M

Board Regular
Joined
Jan 11, 2024
Messages
66
Office Version
  1. 2019
Platform
  1. Windows
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

ورقة عمل Microsoft Excel جديد.xlsx
BCDEF
1TYPE INVOICEDATE
2SALES11/01/2024
3
4NAME
5OMAR
6INVOICE NO
7NN 12220000122
8
9
10
11ITEMIDQTYUNIT PRICEBALANCE
121VGFHJJ-001700.00120.0084,000.00
132VGFHJJ-002200.00130.0026,000.00
143VGFHJJ-003120.00100.0012,000.00
154VGFHJJ-00423.00220.005,060.00
16
17
18
19
20SUM127,060.00
INVOICE NO
Cell Formulas
RangeFormula
F2F2=TODAY()
F12:F15F12=D12*E12
F20F20=SUM(F12:F15)




CUSTOMER sheet


ورقة عمل Microsoft Excel جديد.xlsx
ABCDEF
1DATECUSTOMERDETAILSDEBITCREDITBALANCE
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
ABCDEF
1DATECUSTOMERDETAILSDEBITCREDITBALANCE
211/01/2024OMARSALES INVOICE NO NN 12220000122127,060.00127,060.00
3
4
5
6
7
8
9
10
CUSTOMER
Cell Formulas
RangeFormula
F2F2=D2-E2


when write PURCHASE in C2
ورقة عمل Microsoft Excel جديد.xlsx
BCDEF
1TYPE INVOICEDATE
2PURCHASE11/01/2024
3
4NAME
5OMAR
6INVOICE NO
7PP 12220000199
8
9
10
11ITEMIDQTYUNIT PRICEBALANCE
121VGFHJJ-001700.00120.0084,000.00
132VGFHJJ-002200.00130.0026,000.00
143VGFHJJ-003120.00100.0012,000.00
154VGFHJJ-00423.00220.005,060.00
16
17
18
19
20SUM127,060.00
INVOICE NO
Cell Formulas
RangeFormula
F2F2=TODAY()
F12:F15F12=D12*E12
F20F20=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
ABCDEF
1DATECUSTOMERDETAILSDEBITCREDITBALANCE
211/01/2024OMARSALES INVOICE NO NN 12220000122127,060.00127,060.00
311/01/2024OMARPURCHASE INVOICE NO PP 12220000199127,060.000.00
CUSTOMER
Cell Formulas
RangeFormula
F2F2=D2-E2
F3F3=F2+D3-E3


when write PURCHASE RETURNS in C2
ورقة عمل Microsoft Excel جديد.xlsx
BCDEF
1TYPE INVOICEDATE
2PURCHASE RETURNS11/01/2024
3
4NAME
5OMAR
6INVOICE NO
7PU 2330000188
8
9
10
11ITEMIDQTYUNIT PRICEBALANCE
121VGFHJJ-001700.00120.0084,000.00
132VGFHJJ-002200.00130.0026,000.00
143VGFHJJ-003120.00100.0012,000.00
154VGFHJJ-00423.00220.005,060.00
16
17
18
19
20SUM127,060.00
INVOICE NO
Cell Formulas
RangeFormula
F2F2=TODAY()
F12:F15F12=D12*E12
F20F20=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
ABCDEF
1DATECUSTOMERDETAILSDEBITCREDITBALANCE
211/01/2024OMARSALES INVOICE NO NN 12220000122127,060.00127,060.00
311/01/2024OMARPURCHASE INVOICE NO PP 12220000199127,060.000.00
411/01/2024OMARPURCHASE RETURNS INVOICE NO PU 2330000188127,060.00127,060.00
CUSTOMER
Cell Formulas
RangeFormula
F2F2=D2-E2
F3:F4F3=F2+D3-E3




when write SALES RETURNS in C2
ورقة عمل Microsoft Excel جديد.xlsx
BCDEF
1TYPE INVOICEDATE
2SALES RETURNS11/01/2024
3
4NAME
5OMAR
6INVOICE NO
7SSE 7688-900
8
9
10
11ITEMIDQTYUNIT PRICEBALANCE
121VGFHJJ-001700.00120.0084,000.00
132VGFHJJ-002200.00130.0026,000.00
143VGFHJJ-003120.00100.0012,000.00
154VGFHJJ-00423.00220.005,060.00
16
17
18
19
20SUM127,060.00
INVOICE NO
Cell Formulas
RangeFormula
F2F2=TODAY()
F12:F15F12=D12*E12
F20F20=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
ABCDEF
1DATECUSTOMERDETAILSDEBITCREDITBALANCE
211/01/2024OMARSALES INVOICE NO NN 12220000122127,060.00127,060.00
311/01/2024OMARPURCHASE INVOICE NO PP 12220000199127,060.000.00
411/01/2024OMARPURCHASE RETURNS INVOICE NO PU 2330000188127,060.00127,060.00
511/01/2024OMARSALES RETURNS INVOICE NO SSE 7688-900127,060.000.00
6
7
8
9
10
CUSTOMER
Cell Formulas
RangeFormula
F2F2=D2-E2
F3:F5F3=F2+D3-E3


and voucher sheet when fill in A7 RECEIPT word

ورقة عمل Microsoft Excel جديد.xlsx
ABCD
1DATE
2AMOUNT11/01/2024
310,000.00
4NAME
5OMAR
6CASEVOUCHER NO
7RECEIPTVVR NO 1233333
VOUCHER NO
Cell Formulas
RangeFormula
D2D2=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
ABCDEF
1DATECUSTOMERDETAILSDEBITCREDITBALANCE
211/01/2024OMARSALES INVOICE NO NN 12220000122127,060.00127,060.00
311/01/2024OMARPURCHASE INVOICE NO PP 12220000199127,060.000.00
411/01/2024OMARPURCHASE RETURNS INVOICE NO PU 2330000188127,060.00127,060.00
511/01/2024OMARSALES RETURNS INVOICE NO SSE 7688-900127,060.000.00
611/01/2024OMARRECEIPT VOUCHER NO VVR NO 123333310,000.0010,000.00
CUSTOMER
Cell Formulas
RangeFormula
F2F2=D2-E2
F3:F6F3=F2+D3-E3




and voucher sheet when fill in A7 PAYING word
ورقة عمل Microsoft Excel جديد.xlsx
ABCD
1DATE
2AMOUNT11/01/2024
33,000.00
4NAME
5OMAR
6CASEVOUCHER NO
7PAYINGVVP 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
ABCDEF
1DATECUSTOMERDETAILSDEBITCREDITBALANCE
211/01/2024OMARSALES INVOICE NO NN 12220000122127,060.00127,060.00
311/01/2024OMARPURCHASE INVOICE NO PP 12220000199127,060.000.00
411/01/2024OMARPURCHASE RETURNS INVOICE NO PU 2330000188127,060.00127,060.00
511/01/2024OMARSALES RETURNS INVOICE NO SSE 7688-900127,060.000.00
611/01/2024OMARRECEIPT VOUCHER NO VVR NO 123333310,000.0010,000.00
711/01/2024OMARPAID VOUCHER NO VVP NO 8999_9903,000.007,000.00
CUSTOMER
Cell Formulas
RangeFormula
F2F2=D2-E2
F3:F7F3=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 .
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
You must log in or register to reply here.

Similar threads

E
  • Solved
Ranking Invoices By Highest Amount By "Year to Date"
Replies
2
Views
142
ellison
E
O
  • Solved
Merge data in listbox based on another listbox after search for name and dates
Replies
6
Views
166
Omran Y
O
A
  • Solved
transpose data int listbox on userform based on two dates or without dates
Replies
2
Views
334
Alaa mg
A
A
  • Question Question
Create report by matching part of item and populate for each cell under item across sheets
Replies
2
Views
208
Ali M
A
A
  • Question Question
Merge partial character before specific word across sheets based on headers
Replies
3
Views
186
Amer Omar
A

Forum statistics

Threads
1,223,893
Messages
6,175,246
Members
452,623
Latest member
cliftonhandyman

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top