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

Try closing Excel then restarting Excel. Events may have become disabled.

Like this post
gengiscant

Likes # 0

Shut down Excel, created a new sheet, entered code, code working,saved as 'macro enabled workbook' shut down Excel, reopened saved sheet,code no longer working.

Thanks for your help VoG™ I'll just alter the figures manually.

Like this post
VoG II

Likes # 0

A last thought. On the Developer tab click Macro Security, select Enable all macros and tick Trust Access to the VBA object model.

Like this post
Covergirl

Likes # 0

Please excuse me butting in but I just wondered - why all the code? On initial and subsequent re-reading of the problem, wouldn't this do ?

Like this post
gengiscant

Likes # 0

VoG™.

With your patience I have succeeded. Thank you.

Covergirl.

Don't know who your question was aimed at but if it was for me, then a little more detail would be good.

Like this post
QuizMan

Likes # 0

I usually bow to VoG's infinitely superior knowledge of Excel, but would the following not work?

This assumes the amount owing (£1000 in my example) in A1 with repayments in C1 to C20 as and when they are made. I would have thought the formula in A1 of 1000-sum(c1:c20) would do the trick.

Or have I misunderstood the problem?

Like this post
QuizMan

Likes # 0

Sorry, Gengiscant, cross-posted. Feel free to ignore.

Like this post
VoG II

Likes # 0

@Covergirl

Yes I think it would. My initial understanding was that values entered in a column were to be subtracted from a single cell, hence the need for code.

Like this post
gengiscant

Likes # 0

QuizMan.

If you look at my earlier screen-shots can you adjust your formula to suit and tell me where I would insert it.(keep it clean.LOL).

Same with you Covergirl. Trying to learn as much as I can.

Thanks.

Like this post
Covergirl

Likes # 0

Sorry Gengiscant - that was aimed at you and VoG™ I just wondered, why all the code when a simple formula would do the job?

VoG™ - in reply to your most recent post of 12.01, yes, perhaps it could be interpreted that way, but I took it on face value. Anyway, it appears to be sorted now.

However I did have another read and perhaps this would more exactly fits your requirements

After you had formatted the cells and put in the formulas, the result should look something like this.

Admittedly, I've probably confused everyone now by switching everything to columns A & B. LoL

Like this post

Reply to this topic

This thread has been locked.



IDG UK Sites

Amazon 3D smartphone release date, price and spec: The hologram phone?

IDG UK Sites

iPhone 5s review: why the iPhone 5s is still the best phone you can buy in 2014

IDG UK Sites

Passwords don't work: here's four ways to fix them

IDG UK Sites

Developers get access to more Sony camera features