I have a list of unpaid invoices in two columns.
In A is the original value. In B is the outstanding amount.
Normally payments would be allocated to the oldest debt at the top and
invoices would be zeroed in B as the payments progressed. Zero invoices drop
off.
As often happens, some payments have been allocated to the wrong invoices
and although the overall debt position is the same I cannot use the data in
column B to supply an accurate statement of what is due.
Whilst it is easy to identify where the payments are and how much has been
paid in a separate col I would like to be able to use a formula to show the
position as it should be. As col A still has the original debt and each
invoice appears in it's own row it is not difficult to track down the
payments and the total value. A1 -B1 = payment made or nil.
The hard bit is to have Excel deduct the total of the wrongly allocated
amounts from the old debts until that total is used up.
Simple example
A1 has original 5000.00 and B1 5000.00 bal
B2 has original 5000.00 and B2 2000.00 bal
A3 has original 5000.00 and B3 5000.00 bal
A4 has original 5000.00 and B4 3000.00 bal
etc
The list can be up to 50 invoices.
Here 5000.00 has been wrongly allocated and B1 is still showing as due.
Any ideas?


--
Russell Dawson
Excel Student

Please hit "Yes" if this post was helpful.