# 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 M

##### Excel Ninja
I had to add a helper column, but how does this look?

#### Attachments

• 9.1 KB Views: 127

#### Sally

##### New Member
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

#### Luke M

##### Excel Ninja
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!
Regards!

#### Sally

##### New Member
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.

#### Sally

##### New Member
@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 !!!!!!!!!!!!

#### Luke M

##### Excel Ninja
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.

#### JJ140775

##### New Member
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

• 14.3 KB Views: 13

#### DubaiExcel

##### New Member
Any update to this?

DubaiExcel