Summing like data

[nba]

I have data in the following columns:
a b c d e f
g h
date name unit start end booking payment
balance
01/06/09 John Smith 1 1/10/09 10/10/09 $1000 $100 $900
01/07/09 John Smith 1 1/10/09 10/10/09 $500 $400
15/09/09 John Smith 1 1/10/09 10/10/09 $400 paid
01/04/09 Mary Jane 4 1/11/09 20/11/09 $2000 $1000 $1000
21/05/09 Mary Jane 4 1/11/09 20/11/09 $1000 paid
01/10/09 Fred Mac 2 1/12/09 02/12/09 $200 $200 paid

I need to create a balance sheet for each booking calculating the amount
outstanding. Each time a payment is made a new line is created showing the
date and the amount paid. This is then subtracted from the initial booking
and you are left with the remaining balance owing or the text "paid".

I have tried nested if statements but it is getting too confusing to sort it
out as the number of payments can vary from 1 to many payments.

Can you help simplify a formula to solve this problem.

 

[Stefi]

If your data are sorted by name and date like in your example, then try this:

=INDIRECT("F"&MATCH(B2,B:B,0))-SUM(INDEX(G:G,MATCH(B2,B:B,0)):INDEX(G:G,ROW()))

Or, if you insist on displaying "paid" instead of 0 balance:

=IF(INDIRECT("F"&MATCH(B2,B:B,0))-SUM(INDEX(G:G,MATCH(B2,B:B,0)):INDEX(G:G,ROW()))=0,"paid",INDIRECT("F"&MATCH(B2,B:B,0))-SUM(INDEX(G:G,MATCH(B2,B:B,0)):INDEX(G:G,ROW())))

Regards,
Stefi

„nba†ezt írta:

 

[Ed Davis]

This is now the whole line of code and I am getting a syntax error on the
line.

ActiveWorkbook.SaveAs ThisWorkbook.Path & "\Backup\Sales\Work\"&inval&"(" &
strSH & ")" & Format(Now, " dd-mm-yy hh-mm-ss") & ".xls",
FileFormat:=xlNormal, Password:="", WriteResPassword:="",
ReadOnlyRecommended:=False, CreateBackup:=False

 

[Stefi]

This is another thread, but the answer is that a space has been inserted
manually before and after InVal. VB editor sometimes doesn't recognize & as
an operator that must be separated by a space from the operands:

ActiveWorkbook.SaveAs ThisWorkbook.Path & "\Backup\Sales\Work\" & inval &
"(" & _
strSH & ")" & Format(Now, " dd-mm-yy hh-mm-ss") & ".xls", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False

Stefi


„Ed Davis†ezt írta:

 

[Ed Davis]

Sorry this post was an accident.

 

[nba]

Hi Stefi,

I have tried your formula and it works to a point but when there is more
than one booking for the same person on different dates then the formula
doesn't calculate the correct amount.
What we need to match is the name, unit, start and end. (columns b, c, d, e
in the data below). I have added more data to show the example.

Also once the booking is "paid" I want every balance for that booking to
show "paid". see the last 2 rows of data below. Can this be done?