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
Contact Forum Editor

Send an email to our Forum Editor:


PLEASE NOTE: Your name is used only to let the Forum Editor know who sent the message. Both your name and email address will not be used for any other purpose.

Tech Helproom


It's free to register, to post a question or to start / join a discussion


 

Excel formula required


gengiscant
Resolved

Likes # 0

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.

Like this post
VoG II

Likes # 0

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.

Like this post
VoG II

Likes # 0

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

Like this post
gengiscant

Likes # 0

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.

Like this post
VoG II

Likes # 0

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).

Like this post
gengiscant

Likes # 0

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

Like this post
gengiscant

Likes # 0

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.

Like this post
VoG II

Likes # 0

This is what the code should look like

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

Like this post
gengiscant

Likes # 0

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.

Like this post
VoG II

Likes # 0

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

Like this post
gengiscant

Likes # 0

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?

Like this post

Reply to this topic

This thread has been locked.



IDG UK Sites

iPhone 6 review: best ever iPhone is very good... but no longer the best phone you can buy

IDG UK Sites

Why Apple and Samsung, Google and Microsoft's schoolyard spats make them all look stupid

IDG UK Sites

How to successfully bridge the gap between clients and creatives

IDG UK Sites

How to update your iPhone or iPad to iOS 8: including how to install iOS 8 if you don't have room ()......