Excel 2003 - Help Please

victor2

Another one to add to the pot...
Here's what I'd intended to do earlier with Excel 2000:


No named ranges required. Excel 2000 is limited to 3 conditions, but this appears to apply a "traffic light" type of system, where those overdue or due within 7 days are red, within 10 days are yellow and completed ones are green.
It is easier in Excel 2010 though...:)

mercman1969

Victor.

Tried using your conditional formatting example into my database.
The formatting works...of sorts

All the tiles turn Green??

Some are within the 7 days of being due - would like these to turn Yellow

Those that are within 3 days of the date, or have exceeded the date - would like these to turn Red

Clearly, I MUST be doing something wrong - cant see it anywhere though

victor2

It sounds like the first condition is being met for all cells - once a condition is met, following ones are ignored.
In the example in post #32, the conditions shown are for cell A2 and the first condition refers to its adjacent cell, B2. I seem to remember that by default, when creating a condition referring to another cell, it uses absolute references, where you want relative ones. If your cell address has dollar signs in it, remove them on the first cell and then copy the formula (or just the formatting) to other cells.
Best to get it working for one cell, change its value to test the conditions and then copy it.
Condition 2 would be =TODAY()+3 and condition 3 would be =TODAY()+7 for your requirements. Notice how red takes precedence over yellow.

That may get you going in the right direction. If not, you can always post a screenshot showing the conditions...

mercman1969

Ok......

How on earth do you copy a formula into a call so that it shows the formula??

Am trying to prepare the document to take a screen shot but ensuring it shows up the formulas.....

victor2

You can toggle the display of a worksheet between the results of formulae or the actual formulae themselves by pressing CTRL + ` (grave accent, below Esc on most UK keyboards). Quickest and simplest way.
To just copy one cell formula, edit it and put an apostrophe at the start of the formula. It won't change as you copy it then and you can put it back as it was by removing the apostrophe.

mercman1969

Hopefully....this will let you see my workbook



Been trying on and off most of today to get this conditional formatting right but all i keep getting is all green cells

EchoLocation

I think you may need to post a screenshot of the conditional formatting dialog for a proper diagnosis.

Also, by simply taking a number away from a date it won't take into account weekends and will therefore give you an inaccurate result.

Finally, if you don't want to toggle all of your formulas to be displayed, adding an apostrophe in front of any cell contents will display it as a text value. eg. '=SUM(A1:B2)

victor2

As EchLocation says, a screenshot of the conditional formatting will help identify where the problem lies.
Also, the examples I gave in post #34 did indeed use a simple bit of arithmetic with TODAY(), therefore calendar days. You've already used NETWORKDAYS() in some of your formulae, so could do the same in conditions, unless of course you want a "red" alert to indicate that maybe you need to work a weekend...;)