Allocate a "payment" to a list of "invoices"

[sadaka]

Hi All,

I have a long list of "invoices" like so......

10.00
10.00
20.00
25.00
30.00
20.00
5.00
etc....

Now, i have received a payment for 40.00 - I need excel to work out how i can match 40.00 from that list of invoices. there are several ways i can get to 40.00 like: using both 20.00's - how can i ask excel to work out what i can allocate the 40.00 to but also to prioritise the "invoices" further up in the column. if i get the answer i'd like to suggest using the two 10's and the 20.00 just below it. not use the two 20.00's

Thanks in advance for any responses.

 

[sadaka]

Any responses? Please? Thanks.

 

[jim may]

Before entering an Amount into cell D2:

Excel Workbook
	A	B	C	D
1	Inv Amt	Auto Pymt Applied	Balance After Pymt Applied	Total Receipt
2	10.00	-	10.00	$ -
3	10.00	-	10.00
4	20.00	-	20.00
5	25.00	-	25.00
6	30.00	-	30.00
7	20.00	-	20.00
8	5.00	-	5.00
9
10	120.00	-	120.00
Sheet1

Excel 2007

Cell Formulas
Range	Formula
B2	=IF(SUM($A$2:$A2)<=D$2,A2,IF(SUM($A$2:$A2)>D$2,MAX(D$2-SUM($A$2:$A2),0)))
B3	=IF(SUM($A$2:$A3)<=D$2,A3,IF(SUM($A$2:$A3)>D$2,MAX(D$2-SUM($A$2:$A2),0)))
B4	=IF(SUM($A$2:$A4)<=D$2,A4,IF(SUM($A$2:$A4)>D$2,MAX(D$2-SUM($A$2:$A3),0)))
B5	=IF(SUM($A$2:$A5)<=D$2,A5,IF(SUM($A$2:$A5)>D$2,MAX(D$2-SUM($A$2:$A4),0)))
B6	=IF(SUM($A$2:$A6)<=D$2,A6,IF(SUM($A$2:$A6)>D$2,MAX(D$2-SUM($A$2:$A5),0)))
B7	=IF(SUM($A$2:$A7)<=D$2,A7,IF(SUM($A$2:$A7)>D$2,MAX(D$2-SUM($A$2:$A6),0)))
B8	=IF(SUM($A$2:$A8)<=D$2,A8,IF(SUM($A$2:$A8)>D$2,MAX(D$2-SUM($A$2:$A7),0)))
B10	=SUM(B2:B9)
C2	=A2-B2
C3	=A3-B3
C4	=A4-B4
C5	=A5-B5
C6	=A6-B6
C7	=A7-B7
C8	=A8-B8
C10	=SUM(C2:C9)
A10	=SUM(A2:A9)

After Entering an amount into cell D2 (say $70.00)

Excel Workbook
	A	B	C	D
1	Inv Amt	Auto Pymt Applied	Balance After Pymt Applied	Total Receipt
2	10.00	10.00	-	$ 70.00
3	10.00	10.00	-
4	20.00	20.00	-
5	25.00	25.00	-
6	30.00	5.00	25.00
7	20.00	-	20.00
8	5.00	-	5.00
9
10	120.00	70.00	50.00
Sheet1

Excel 2007

 

[VoG]

This might help you http://www.tushar-mehta.com/excel/templates/match_values/index.html

 

[sadaka]

Thanks very much guys. I'll test it all laterEu