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