Allocate payments
in [General]
|
From: Russell Dawson on 9 Apr 2010 12:06 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.
|
Pages: 1 Prev: Enable/Disable Macro Pop-Up Next: Creating emailing list from column of addresses |