Mohammed Shafeequ
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!
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!