• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Allocate a Payment to Several Invoices. Returns Partial Payment, Invoice Paid, Invoice Paid Date

Sally

New Member
Hello
I have 2 tables, first table contains many invoices in date order. The second table contains payments. Some payments can be for more than 1 invoice. Some payments can be for part of an invoice. Oldest Invoices are Paid first.
What I want to do is to create 2 additional columns on the invoice table. One column having paid, not paid or partially paid and the second column with the date it was paid.
I want the the formula to be able to look over at the payment table and tell me that it is paid or not.

The hard part is this: The payment table has several different payments on different dates. So the formula needs to know if a payment is already used up by previous invoices and skip to the next payment.

Example; Invoice A = 50, Invoice B= 30, Invoice C= 70. Payment 1= $70 paid Jan 1, Payment 2= $60 paid Feb 28.
Payment 1 would cover Invoice A and part of Invoice B. When i enter Payment 2 the Feb 28 of $60 on the row below payment 1, I want the formula to automatically know to finish paying the Invoice B and go on to invoice C.

The outputs for the example above would have; "Paid" "Jan 1" for Invoice A. "Partially Paid" "Jan 1" for Invoice B. When February 28 payment is entered. Invoice B would change to "Paid" "Feb 28". Invoice C would have "Partially Paid" "Feb 28".

I know it was a bit long but I wanted to be clear. Thanks for the Help Guys!
Cheers!
 
Luke you are awesome, thanks! adding the helper column I get it, also for some reason I kept trying to use vlookup instead, trying to figure what value would i use to return the date?? lol I must say though its the first time I have seen 9E+99 actually used. Do you mind giving me a short summary on that? From me a mid level excel user to a Excel Ninja!
Oh yeah and i did my happy dance in my office at my computer :)
 
No problem Sally. As I noted in the book, there will only be one line that has a "Partially Paid" cell. We also know that it will be partially paid up to the last date of a payment. So, what we really just needed to know is the date of the last entry on the Payments sheet. To do that, I just told the LOOKUP function to find a really large arbitrary number. LOOKUP then goes to the end of the range, basically says "Well, I couldn't find the large number, but this is the last number I looked at!" and returns the date.
 
@Sally
Hi!
And what about uploading the happy dance video? We're waiting eagerly for it...
Regards!
 
Thanks Again, one problem though when i pay the exact amount i cant get the correct date. example invoice 50 payment 50 than date gives me 0 Jan 00.

Tried to correct it but it didn't work out.
 
@SirJB7 :) I guess you'll just have to imagine that one, lets just say at least I was in Rhythm!
Everybody need to do a happy dance at work once in a while :)!!!!!!!!!!!!
 
Thanks Again, one problem though when i pay the exact amount i cant get the correct date. example invoice 50 payment 50 than date gives me 0 Jan 00.

Tried to correct it but it didn't work out.

Oops, looks like you're right. Change the formula on Invoice sheets, cell D2 to this and copy down:
=IF(C2="Partially Paid",LOOKUP(9E+99,Payments!A:A),IF(C2="Paid",INDEX(Payments!A:A,SUMPRODUCT(MIN((SUM(B$2:B2)<=Payments!$C$2:$C$400)*ROW(Payments!$C$2:$C$400)))),""))
That should fix the problem.
 
Having trouble using the above formula for netting credit card payments against transactions. Please can you help!! I've attached a spreadsheet for your perusal.
 

Attachments

  • Invoice_Example (3).xlsx
    14.3 KB · Views: 45
Back
Top