Too much condition to pull information invoice from multiple sheets

O

Omar M

New Member
Joined
Jan 11, 2024
Messages
2
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

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
You must log in or register to reply here.

Similar threads

A
  • Solved
transpose data int listbox on userform based on two dates or without dates
Replies
2
Views
149
Alaa mg
A
O
  • Question
Copy selected item from combobox on userform to specific column for multiple words
Replies
0
Views
120
Omar M
O
K
  • Question
Change code from inside sheet to userform directly
Replies
0
Views
164
KalilMe
K
A
  • Question
sorting duplicates Customers names by add two formulas in last column
Replies
3
Views
197
Kerryx
K
A
  • Question
copy amount before TOTAL row for specific column based on matching names in column with sheet name
Replies
0
Views
140
Alaa mg
A

Forum statistics

Threads
1,215,076
Messages
6,122,983
Members
449,092
Latest member
Mr Hughes

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