invoices receiving balance amount on post dated chq issue

HI all,
i am working in invoices balances of customers. we are providing goods on credit. after some time customer give a chq# post dated ( means mention on specific date not before present the bank prior to this date) of any bank and i have to adjust customer receiving amount. i have two sheet one is invoices receiving amount and other is customer chq# detail. when customer give us chq# then specific date amount will be adjusted. please see the file attached for your understanding. i also filter that one to easy for you. if you have any problem please ask me.
Thanks.

Hi Avk,
Thanks for the reply, i am confused your working because result is not as my wish. i do so many changes in my original to understand my point on easy way. please focus one customer then go to other. i calculate sheet2 working till 2-15-2019 mac corporation when i got chq. focus on chq date received. in sheet1 red highlight cells ignore because 2-15-2019 is last date of issuance to mac corporation and same day we received chq so i have to adjust invoices based on chq amount on FIFO method.
stop at 2-15-2019 and calculate 55000 amount received from mac corporation first then goto next date. if you want to remove red highlighted rows so you can do it.
see my calculation sheet2 red border calculation.
after doing this we goto next customer and next chq for the same customer.
i hope you can understand my point. if you have more information about this please ask me. i will be glad.

see my new file.

i also filter mac corporation

Thanks

Hi Avk,
any help?

Hi Avk,
i got it your point, thanks
i would like to highlight RED color on those invoices which were adjusted by chq amount because i have to make aging report after this invoices but there is a problem Mac Corporation chq amount 55000 adjusted invoices FIFO method. the invoices 11122 is partial adjust of this amount. see the snap. can you please do it by conditional formatting RED color. same condition applies for other parties.
second if you have any other idea for this so you can do it because formula and Pivot Table not apply at color based cell to calculate partial invoices amount. data is too big.
Thanks
file is attached.

Hi Avk,
how i can do it with conditional formatting RED color invoices but last invoice - 11122 will be partial. is it possible to change this any other color for partial last invoice?
Thanks.

Hi Avk,
there is no any possibility?

Hi JeteMC,
Thanks for the reply,
kindly do not consider column I and J sheet1 because its a manual working for understanding which invoice will be partial on the amount of 55000 Mac Corporation. when i delete value on column I and J then conditional working will not work. please consider working to understand what i want. invoice 11122 will be partial invoice after adjustment of 55000 cheq received from Mac corporation. so i want it highlight with different color for partial invoices. same condition applies for other parties.
new file is attached.

Thanks.

Hi JeteMC,
Thanks for the reply,
You are really a champion, its like a big cheer, Thanks Boss. You did it.
Can you please tell me one more thing, please see the column I,J,K,L where mention Chq # and Bank Name. I want copy both till the last transaction record of chq amount of party. Kindly tell me how to handle last invoice 11131 where is partial of Mac corporation chq no 333301 and MEEZAN BANK amount of 72000 then next chq will adjust full of this invoice dated on received 4-3-2019 amount of 70000. I need mention both chq number and bank name. Is it possible by same column or need to create one column for this? Please see the update file.
When you delete amount chq # 999901 you can understand my point easily.
Same condition apply for other parties.
Thanks a lot,

Hi JeteMC,
Thank for the reply,
There is a little bit problem for partial invoice, please see the last transection of partial invoice Mac Corporation invoice # 11134 show national bank, its mean its partial, again I received the chq # MCB 666601 amount 1100 then it is not showing next column sheet1 K36 and L36. Please put amount sheet2 1100 then check. When I put the amount 1500 then its show MCB. Why not show both bank name of every last partial invoice?
Second, first put 1100 amount MCB bank in sheet2 then received next chq# 888801 UBL amount 500 then also not showing both bank of last partial invoice.
I need to show every last partial invoice bank name of every client to see the how many bank involved for partial invoice to complete. Please see the invoice 11131 treatment.
I hope you understand point, if you need more clarification so you ask me.
Thanks again for the reply.
Thanks.

Hi JeteMC,
you are great, you did it, welcome my boss,
Thanks a lot,
kindly mention last thing that, i want to show more than two bank show for partial invoice so kindly tell me i just copy the formula column K and L then paste it into M,N,O P column or you need to change this any where of formula? please see the last transaction of Mac corporation.
please see the file.
Thanks.

Hi Boss,
Thanks for the reply,
But again you can see the last transaction which i mention on my update file new bank Name TTR is not showing column O42 and P42 for mac corporation. kindly tell me why this not showing?
Thanks,
please see the file.

Hi JeteMC,
Great Work Boss,
You are really a master of excel. its like a big cheer.
Thanks a lot.