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]

Below are the codes i'm using



=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


In my previous reply, yes the entry would have been in order from 1,2,3 etc. If i enter an amount in cell B16 and say B18, both in the amount of $77 but then back out B16 and enter $78, thats when it happens. Thanks for your help since i don't know how to create VBA codes.

 

[crimson_b1ade]

if you plan on correcting past items then you need a sequence number in your spreadsheet see below example which has the formula you'll be using(vba code to insert the sequence numbers to follow in the next post):

Book1.xlsm
	A	B	C	D
1			CashBal:	2,000.00
2
3
4
5
6
7
8
9
10
11
12
13
14
15	Bill	Amount	Total	Sequence
16	House	1,500.00	$500.00	1
17	Car	200.00	$300.00	2
18	CreditCard1	350.00	($150.00)	4
19	CreditCard2	100.00	$200.00	3
20	CreditCard3
Sheet2

 

[crimson_b1ade]

vba code to add sequence numbers:

Private Sub Worksheet_Change(ByVal Target As Range)
 
If Target.Column = 2 And Target.Row >= 16 And Target.Row <= 35 And Cells(Target.Row, Target.Column + 2) = "" Then
    Cells(Target.Row, Target.Column + 2) = Application.WorksheetFunction.Max(Range("D16:D35")) + 1
  Else
     'do nothing
End If
 
End Sub

 

[crimson_b1ade]

by the way, if you don't want the sequence numbers to be visible, then you can change the text font color to 'white' and it will blend in with the cell's white background and become invisible.

 

[MarkDShark]

So these codes will replace the ones already incorporated or are these additions to those?

 

[MarkDShark]

I used the codes in your new posts alone, but the only field that shows a figure is C16, the sequence does show when another entry is entered in column B after C16, but no other money amounts appear.

The columns i'm using are "A" for the payment type, "B" for the payment amount, "C" for the calculated balance after a payment is entered and "D " is now used for the sequence. I entered the Formula in C16 as instrcutioned, and cut and paste the VBA as typed in your post. Seems like it should have worked.

 

[MarkDShark]

I just noticed a few things in your post that i didn't see earlier. I will get back to you on this. Will try to figure out what i did wrong then comment on the results. Thanks

 

[crimson_b1ade]

yes, it replaces, so delete the old code I gave you.

So these codes will replace the ones already incorporated or are these additions to those?

 

[crimson_b1ade]

yeah, Column C now uses a SUMIF function =IF(B16<>0,$D$1-SUMIF($D$16:$D$35,"<="&D16,$B$16:$B$35),"")

and I deleted cell D2 as it was no longer needed.

I just noticed a few things in your post that i didn't see earlier. I will get back to you on this. Will try to figure out what i did wrong then comment on the results. Thanks

 

[MarkDShark]

I got it to work perfectly. However, the sequence numbers would not resequence when i backed out a payments entry. I fixed that by adding in a clear button to clear the sequence column and all other entries entered. I also had to unlock columns C & D and link Column E to the corresponding cells so that i could hide columns C & D since the clear button would not work with those columns unlocked. I assume the VBA code was preventing it from clearing. After the little details, the clear button works fine in clearing the sequence numbers so the order starts over. So far have not noticed anything thats not working how i wanted it to work. Thanks for all your help on this matter. I really need to learn VBA and how to create modules.