Money Balance sheet

[MarkDShark]

I need to create a working table that will deduct payments from a cash balance as i enter the payment amount in any cell even if it's out of order. If i made a payment for "car", i want the balance to show in the last cell and if i make a payment for "house", then i want the balance to show there as well after deducting the payment and so on and so on. I want it to be able to calculate passed the cash balance to a negative value which would tell me which field caused the neg entry which would mean the funds weren't there to cover the payment amount and would should in red the amount still owed. Below is the example box,

<TABLE style="WIDTH: 245pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=326 border=0 x:str><COLGROUP><COL style="WIDTH: 74pt; mso-width-source: userset; mso-width-alt: 3620" width=99><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 58pt; mso-width-source: userset; mso-width-alt: 2816" width=77><COL style="WIDTH: 65pt; mso-width-source: userset; mso-width-alt: 3145" width=86><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #40ff40; BORDER-TOP: #40ff40; BORDER-LEFT: #40ff40; WIDTH: 74pt; BORDER-BOTTOM: #40ff40; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=99 height=17></TD><TD style="BORDER-RIGHT: #40ff40; BORDER-TOP: #40ff40; BORDER-LEFT: #40ff40; WIDTH: 48pt; BORDER-BOTTOM: #40ff40; BACKGROUND-COLOR: transparent" width=64></TD><TD style="BORDER-RIGHT: #40ff40; BORDER-TOP: #40ff40; BORDER-LEFT: #40ff40; WIDTH: 58pt; BORDER-BOTTOM: #40ff40; BACKGROUND-COLOR: transparent" width=77 x:str="Cash Bal: ">Cash Bal: </TD><TD class=xl23 style="BORDER-RIGHT: #40ff40; BORDER-TOP: #40ff40; BORDER-LEFT: #40ff40; WIDTH: 65pt; BORDER-BOTTOM: #40ff40; BACKGROUND-COLOR: transparent" width=86 x:num="2000">$2,000.00</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #40ff40; BORDER-TOP: #40ff40; BORDER-LEFT: #40ff40; BORDER-BOTTOM: #40ff40; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17></TD><TD style="BORDER-RIGHT: #40ff40; BORDER-TOP: #40ff40; BORDER-LEFT: #40ff40; BORDER-BOTTOM: #40ff40; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #40ff40; BORDER-TOP: #40ff40; BORDER-LEFT: #40ff40; BORDER-BOTTOM: #40ff40; BACKGROUND-COLOR: transparent"></TD><TD class=xl23 style="BORDER-RIGHT: #40ff40; BORDER-TOP: #40ff40; BORDER-LEFT: #40ff40; BORDER-BOTTOM: #40ff40; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl25 style="BORDER-RIGHT: #40ff40; BORDER-TOP: #40ff40; BORDER-LEFT: #40ff40; BORDER-BOTTOM: #40ff40; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Bill</TD><TD class=xl25 style="BORDER-RIGHT: #40ff40; BORDER-TOP: #40ff40; BORDER-LEFT: #40ff40; BORDER-BOTTOM: #40ff40; BACKGROUND-COLOR: transparent">Sequence</TD><TD style="BORDER-RIGHT: #40ff40; BORDER-TOP: #40ff40; BORDER-LEFT: #40ff40; BORDER-BOTTOM: #40ff40; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #40ff40; BORDER-TOP: #40ff40; BORDER-LEFT: #40ff40; BORDER-BOTTOM: #40ff40; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>House</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num>

1​

</TD><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num="500">$1,500.00</TD><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num="1500">$500.00</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Car</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num>

2​

</TD><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num="200">$200.00</TD><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num="1300">$300.00</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Credit Card 1</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num>

4​

</TD><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">$350.00</TD><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">$150.00</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Credit Card 2</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num>

3​

</TD><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num="300">$100.00</TD><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num="1000">$200.00</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Credit Card 3</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num>

5​

</TD><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"></TD><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17> </TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17> </TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17> </TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17> </TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17> </TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17> </TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD></TR></TBODY></TABLE>

The payments are out of order based on which would have to be paid first based on a due date. In red is the negative balance for money short on a bill and where it lays on payment 4. The squence is for visual purposes to show you what i was talking about being able to calculate the balance no matter which entry i enter 1st or 4th. It would be based on any order i enter.

 

[MarkDShark]

Oh i thought the link i see in the post was part of your post. Are there formulas listed in the B & C columns or does the VBA do it all? I do see the formula listed top left corner, but what is the copy formula botton do? Its in the top right corner of the post. I clicked on it but im not sure whats its supposed to do.

 

[crimson_b1ade]

there's no formulas in the spreadsheet other than in cell D2. You can just copy the formula from the formula bar in the upper left corner.

Then you need to right-click the sheet, select 'view code', then paste the vba code in post# 9.

 

[MarkDShark]

Got it! Works like a charm and how i wanted it to work. Took me awhile but it finally clicked. Thank you sir!

 

[MarkDShark]

The only problem im having with this, is when i back out an enty made in column B, the corresponding column C totals cell does not clear. I need column C to remain blank until i entry a payment, the clear when if i remove the payment if i entered it in the wrong payment filed in column B. Is there something i can add to the VBA that will fix this? I don't know VBA coding so appreciate any help you can give me. Thanks again.

 

[MarkDShark]

Oh and another thing, i need it to target only rows 16 through 35 in the VBA. There are other parts of the spreadsheer above what i am trying to create that i don't want to change. I noticed the VBA is changing everything in every cell for column C. I only need it to work for rows of my chosing and in this case, rows 16 through 35. I almost have it, just a few things left to debug. Hope your able to help.

 

[crimson_b1ade]

The below updated code will allow you to delete. You will also want to change your formula in cell D2 to =D1-SUM(B16:B35)

Private Sub Worksheet_Change(ByVal Target As Range)
 
If Target.Column = 2 And Target.Row >= 16 And Target.Row <= 35 Then
    Cells(Target.Row, Target.Column + 1) = Range("D2").Value
  Else
     'do nothing
End If
 
For i = 16 To 35
    If Range("B" & i) = "" Then
          Application.EnableEvents = False
          Range("C" & i) = ""
          Application.EnableEvents = True
       Else
          'do nothing
    End If
Next
End Sub

 

[MarkDShark]

Now its complete! Thanks for all your help. Now i can finish building the rest of the sheet. Thanks again!

 

[MarkDShark]

I have it all together, but the only problem that i'm having now, is if i entered the wrong payment amount and back space it out to enter the correct amount, it takes the total balance and recalculates it from the beginning instead of taking into consideration the other payments entered.

Example, i entered $55 dollars for 1 payment, then i entered $60 for another but the $60 was incorrect and should have been $61. I back spaced it out to delete it, then entered the correct amount of $61 and it will duplicate the total of what i get when i entered the $55 payment. Seems to recalculate all over again. I hope your understand this.

 

[crimson_b1ade]

I can't re-duplicate your issue. Did you change the code or formula? If I follow your steps and put in $55 I get a balance of $1945. Then I put in $60 I get a balance of $1,885, when I back out the $60 (using backspace, delete or by overwritting the $60) and put in $61, I get a balance of $1,884 (which is correct).

 

[crimson_b1ade]

what won't work though (since I didn't code it to do) is if you put in numbers #1, #2, and #3 then you try and go back and correct #2. Things get out of wack then. You can't go back and correct prior sequences. You can only correct the last number in your sequence. If you need to correct prior sequence numbers, then it will be a bit more involved since you'll essentially need to loop through the remaining sequence from the correction point and re-adjust the balance. Since we're not keeping track of the sequence the numbers are being inputted, that will have to be coded if you want that ability to go back and correct previous inputs (i.e. not just the last inputted number).