Excel 2007 problem

  Dirty Dick 10:59 31 Jan 2008
Locked

I’m running Excel 2007, with Vista. I have a problem trying to set up a spreadsheet to show payments made. Is there any way that when a date is reached I can get a cell to change colour and put the info in that cell, into another cell, to show when a payment has either been made, or is shown overdue? I think it may be Conditional Formatting, but am struggling to come up with a solution.
Thanx
DD

  PalaeoBill 11:20 31 Jan 2008
  Picklefactory 11:34 31 Jan 2008

click here
Sounds fairly similar to this one. Re: transferring contents of one cell to another based on date it would be simple IF statement I think.
Could do with a little more detail, what exactly do you want to see in which cells, try and give an example please?

  Dirty Dick 12:07 31 Jan 2008

Assume that A1 is Date Due, A2 is Amount Due, somewhere on the sheet is a cell that all amounts that have been paid are totalled. I want the Date Due cell to change colour if it is overdue, and is there a way of ticking a cell to say that it has been paid, changing the Amount Due to a “Paid Colour”?

  Picklefactory 13:47 31 Jan 2008

OK, if your sheet can accommodate, it might be easier to use columns. So
Column A = Due Date manually input eg
A1=29/1/08
A2=31/8/08
A3=1/2/08
Column B = Amount Due manually input (Don't enter these values until after you've done the formatting below) eg
B1=£50
B2=£50
B3=£50
Column C = Paid Format font for this column to Monotype Sorts, which is a symbol font where number 4 will display as a tick
C1=Empty (Payment not made yet)
C2=4 (Tick displayed)
C3=Empty
I've used two conditional format rules to do this, highlight cell B1 and open conditional formatting
In New Rule, choose use a formula at the bottom of the list and enter =AND(A1>0,A1<TODAY(),C1<4)
into the formula area, and set fill to RED in format area
Open another new rule and, again, in the formula section enter =C2=4 and set fill to GREEN in format area.
Click and drag cell B1 down your column to copy the format down.
That should sort the highlighting
For the totalling of all paid items, simply paste =SUMIF(C2:C4,"=4",B2:B4) into your total cell, where C2:C4 = range of cells containing ticks and B2:B4 = range containing amounts.
See how you go.

  Picklefactory 13:49 31 Jan 2008

Should have mentioned, when payment is received, simply type the number 4 into Paid cell.

  Picklefactory 14:08 31 Jan 2008

=C2=4 should be =C1=4
and
=SUMIF(C2:C4,"=4",B2:B4) should be =SUMIF(C1:C4,"=4",B1:B4)
Sorry, I had headings in row 1 and intended to alter to suit example cells.

  Dirty Dick 14:01 05 Feb 2008

Sorry for delay in getting back, been away for weekend.

I don't have Monotype Sorts font. Is there any other alternative?

  Picklefactory 10:16 12 Feb 2008

If you want to email me direct, I can send you the font (Click on yellow envelope, just in case you've never used it)

  Noldi 11:17 12 Feb 2008

Widdings font type "a" note lower case

Noldi

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

Elsewhere on IDG sites

Xiaomi Mi Mix 2 review

Halloween Photoshop & Illustrator tutorials: 20 step-by-step guides to creating spook-tacular…

iPhone X news: Release date, price, new features & specs

Comment créer, modifier et réinitialiser un compte Apple ?