If you cannot get this working in your current version of Excel, you will not
be able to get it working in Excel 12. There is something wrong with your
data, or your implementation, and that will not be changed by changing Excel
versions.

As I wrote, the limit on conditional formats is three(3) and you only have
three (3) conditional formats listed (green, orange, and red).

So what does "doesn't work" mean?
Did Excel crash?
Did you get some error message?
Something else?

What cell contains the conditional formatting?

What, exactly (copy/paste the formula please) is in each condition in the
dialog box?

What cells did you use for "Drivable" and "ET" and what, exactly, is contained
in those cells?

It worked fine here.

We should be able to figure out the problem in your system.

Best,
--ron



On Sat, 12 Nov 2005 09:11:03 -0800, Stuart
wrote:

ron do you mind explaining how i use this formulae currently as i put it in
the conditional formating box and it doesnt work please help. if no solution
is posted i will just have to advise my managing director to buy office 12
when its released next year to better serve our customers.

"Ron Rosenfeld" wrote:

On Sat, 12 Nov 2005 03:55:02 -0800, Stuart
wrote:

i want a conditional formatting system that is not limited like the current
one. currently i can only set 3 conditions and the formating settings for
each condition being true in the following scenario i would require 6
conditions that would require the values of more than 1 row/column to be of a
specific value to create the desire effect. they are as follows:

1) if drivable is = yes and elapsed between 0 and 6 cell shadow green
2) if drivable is = no and elapsed between 0 and 3 cell shadow green
3) if drivable is = yes and elapsed between 6 and 9 cell shadow orange
4) if drivable is = no and elapsed between 3 and 9 cell shadow orange
5) if drivable is = yes and elapsed greater than 9 cell shadow red
6) if drivable is = no and elapsed greater than 9 cell shadow red

in theory 5 and 6 could be merged into one condition of

if elapsed greater than 9 cell shadow red

however if only 5 options were available is doesnt allow for you to set
different thresholds for red depending on drivable or non drivable and at
present i dont know how my company wishes to proceed. i would like for this
enhanced kind of conditional formatting to be added to office 12.


Actually, although you have more than three *conditions*, you only have three
*conditional formats*, (plus you could set the cell that meets no conditions to
a baseline format), so you can do this without waiting for Excel 12.

Something like:

Condition 1 Formula Is:

=OR(AND(Drivable="yes",ET0,ET<=6),AND(Drivable="n o",ET0,ET<3))

Condition 2 Formula Is:

=OR(AND(Drivable="yes",ET6,ET<9),AND(Drivable="no ",ET3,ET<9))

Condition 3 Formula Is:

=AND(OR(Drivable="yes",Drivable="no"),ET9)


Also, I noted that you did not specify what you wanted to occur if elapsed time
was exactly equal to 0,3 or 6 (depending on drivable state), or 9 so I left
that as the baseline format.

You will want to change some of the comparison operators if you want something
other than what you specified.


--ron


--ron