Excel formula required

  gengiscant 16 Nov 11
Locked
Answered

I want to keep track of a unexpected debt that I have been landed with by my council so have created a spreadsheet which has the overall amount owed date of payment and amount paid. What I would like to happen is when I enter an amount paid it is automatically deducted from balance owed.

I hope there is enough info there to answer my query.

  VoG II 16 Nov 11

You'll need some code for that. Assuming that the sum owed is in A1 and that you are entering payments in column C try this. Right click the sheet tab, select View Code and paste in

Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column = 3 Then Application.EnableEvents = False Range("A1").Value = Range("A1").Value - Target.Value Application.EnableEvents = True End If End Sub

Then press ALT + Q to close the code window.

  VoG II 16 Nov 11

That hasn't posted correctly

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Column = 3 Then

Application.EnableEvents = False

Range("A1").Value = Range("A1").Value - Target.Value

Application.EnableEvents = True

End If

End Sub

  gengiscant 16 Nov 11

VoG™

Sorry for my stupidity but I am not sure exactly what I should be copying from your second post, is it just the lines on the left? or every thing after That hasn't posted correctly?

If its any help at all the first payment will be entered in cell G12 and the first deduction from the balance will be in K12.

  VoG II 17 Nov 11

Everything after That hasn't posted correctly.

If Target.Column=3 - Change 3 to the column number where you are entering payments.

A1 - change to the cell that holds the balance (twice).

  gengiscant 17 Nov 11

Thanks will try this later and get back to you on my results.

  gengiscant 17 Nov 11

VoG™.

Before. This shot shows the sheet in question and the code which I entered, with what I thought were the right cells.

After. As you can see rather than deducting the figure 90 from the overall balance each time it is just totaling this figure. I wish to see the overall figure reducing so that I can see instantly what remains.

Thanks for your help so far.

  VoG II 17 Nov 11

This is what the code should look like

http://i40.tinypic.com/2u7qb05.png

  gengiscant 17 Nov 11

VoG™.

That certainly works,but,when I come to try and save it I get the following Saving.Tried yes and know but I am missing a step somewhere. I am sorry I did not make myself clear I absolutely know nothing of Excel, so can only apologise for not knowing the basics.

One other thing is it possible that the balance total to actually move down the sheet as more and more payments are added? Not a problem if this is not possible. Just me being lazy.

  VoG II 17 Nov 11

You need to Save As and specify macro enabled workbook (*.xlsm).

  gengiscant 17 Nov 11

I am losing the will to live here. I have followed your instructions but after saving the sheet, code no longer seems to work.See screen-shot.

My sanity is ebbing As you can see, the sheet after code entered, the code and the save screen.

Again I pose the question.What am I doing wrong?

Advertisement

This thread is now locked and can not be replied to.

Should I upgrade to Windows 10? 8 reasons why you should upgrade to Windows 10... and 2 why you…

We are being sold the ability to spend money we don't have. And we love it

Encore creates eerie effects for Spielberg's new TV show The Whispers

How to set up Apple Music & make sure you don't get charged: Get Apple Music for free

We use cookies to provide you with a better experience. If you continue to use this site, we'll assume you're happy with this. Alternatively, click here to find out how to manage these cookies

hide cookie message