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.

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:

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.

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?

"Stefi" wrote:

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:

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
01/10/09 John Smith 9 1/12/09 5/12/09 $900 $100 paid
01/11/09 John Smith 9 1/12/09 5/12/09 $800 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.

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

--
Thank You in Advance
Ed Davis
"nba" wrote in message
...
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.

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:

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

--
Thank You in Advance
Ed Davis
"nba" wrote in message
...
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.

Sorry this post was an accident.


--
Thank You in Advance
Ed Davis
"Stefi" wrote in message
...
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:

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

--
Thank You in Advance
Ed Davis
"nba" wrote in message
...
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.