Excel formula required

  gengiscant 16:10 PM 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:19 PM 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:21 PM 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 17:38 PM 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 07:04 AM 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 07:24 AM 17 Nov 11

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

  gengiscant 09:34 AM 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 09:45 AM 17 Nov 11

This is what the code should look like

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

  gengiscant 10:25 AM 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 10:32 AM 17 Nov 11

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

  gengiscant 10:53 AM 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.

Sony Xperia Z5 review: Hands-on with the phone which the Z3+ should have been

1995-2015: How technology has changed the world in 20 years

Get your photos turned into illos by some of the world’s best artists

What to expect at the September Apple event: Apple announces 9 September event, sends out invites