More help on Excel wanted please

  Ray5776 20:56 12 Dec 2006
Locked

Hi Everyone,

I am fairly new to Excel and very impressed with it so far, the trouble is having found the basic spreadsheet principle relatively easy the more complex issues I find are far from it.

I will try to explain what I want to do and welcome your advice and suggestions.

Say Col N Is oustanding invoices and Col L is payments made.

Entries in Col N (outstanding) to be in red font until payment is made, then to go to black font colour.

This is not as simple as it seems
Example Col N £250
£250
£261
Payment of £511 is paid but it has to relate to the first dated £250 and the £261.

Another example

Col N £3.60
£5.00
£8.70

Col L has a payment of £15.00, I want this to show as an error.

Col L must equate to any combination of sums in Col N but in date order or show as error.
I have used 3 figures but fifteen would be about the norm.

I expect someone to say "conditional formatting"
but I need a little more guidance on this one please.

Not an easy thing to explain either.




ray

  VoG II 21:08 12 Dec 2006

Does each row correspond to a single transaction or could cost/payment for one purchase appear on multiple rows?

If they are on single rows then Conditional Formatting should work. If you are looking at e.g. a PO number in another column and costs/payments that refer to that in other rows then things get complicated.

Why is

Col N £3.60
£5.00
£8.70

an error?

  Ray5776 19:30 13 Dec 2006

Hi VoG,
as I said I find this difficult to explain and I think it will be complicated, part of the "fun" of learning.

Col N. £3.60
£5.00
£8.70

should show as an error as a payment of £15.00 does not relate to any of these invoice combinations.

Ref your first point, I am not sure what you mean here, what I want to do is that when I receive a payment of X amount it looks through all outstanding invoices (on earlier lines) and equates the ammount paid to the invoices.

Example is:

owing £1 £2 £3 £4 £5 £6

paid £10

This has to relate to the first dated invoices i.e. £1 £2 £3 £4 not £4 + £6 or any other combination if you see what I mean.


Thanks for your help yet again, I find this sort of thing really interesting as there is allways a way, it is just a matter of finding it.

Ray

  VoG II 20:11 13 Dec 2006

OK, this sort of thing is almost impossible to describe (or visualise from the description) without being able to post an example, which is not possible here.

Alternatives:

1. Click my yellow envelope to send me a message, I will reply, then you can send me an example as an attachment. Whatever you do, please do not send me your real financial data!! It would be usefull if you could colour the data by hand so that I know what you are expecting to achieve.

OR

2. Sign up at MrExcel click here

where you can post an example - see click here

  Ray5776 18:45 14 Dec 2006

Hi VoG,
thanks for your continued support, I will have a re-think on this and send you an example of what I am hoping to achieve, the difficult part is explaining it hence the few replies I would think.
I would of course only send an example and not my real financial data , I agree with you entirely on this just as I would not wish to receive other peoples personal finances.

Ray

  Ray5776 18:39 15 Dec 2006

Hi VoG,
I do not think I am going to acheive what I wanted to do here, one company sends me payments unrelated to any invoices (Dont ask me why) wily nily cheques for any bizzare amount be it over or under.

I think the easiest way to do this is that when a payment is made any oustanding invoices equal to or less than the payment are cleared (By changing the font to black) anything else remains red as outstanding even if the payment has covered part of it.

If an overpayment is made then their oustanding balance can show a credit.

This looks much easier to me than my original ideas.

Can I have your valued opinion on this please and any suggestions.

Thanks Ray

  VoG II 18:43 15 Dec 2006

Hi Ray.

What you are suggesting sounds do-able. However, I still think that you should have a go at posting a small example on MrExcel. Others must have been faced with this sort of problem and MrExcel is where the real Excel experts hang out.

  Ray5776 20:43 15 Dec 2006

Thanks VoG, I will give your link a try (MrExcel)
I allways use this site as a starting base as I find it very friendly and helpful.
I have some of my own ideas now as to how to do this but knowing I can get a bit of back up from here or the links is great.
Learning Excel is more of an interest to me than a necessity, if I cant do it I will write it in a book with a pen (remember those times when life was so easy) :-)

Thanks Ray

  Ray5776 18:11 16 Dec 2006

This should be easy now that I have changed the requirements to something far less complicated.
I thought using conditional formatting if N20 (outstanding) is greater than L20 (payments) then format font in N20 to red.
ie cell value N20 is greater than. =L20
but this does not work.
If I enter the actual figure in L20 then it will work
but Col L is constantly changing.
I need conditional formatting to refer to the cell in Col L and its current entry.

  VoG II 18:17 16 Dec 2006

I'm not sure why that isn't working. In the conditional format dialog box Click the arrow to change from Cell Value Is to Formula Is and enter the formula

=N20>L20

  VoG II 18:34 16 Dec 2006

I may know why your approach didn't work. In the Cell Value Is check that the condition is

=L20

in the right hand box. Excel sometimes surrounds the expression with " quote marks.

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

Elsewhere on IDG sites

Alienware 17 R4 2017 review

Is this the future of VR and AR?

Best iPad buying guide 2017

Comment regarder le Bureau des Légendes en ligne ?