thanks ron it works almost perfectly except when drivable is no and elapsed
is 3 then it dont work. any ideas why?

"Ron Rosenfeld" wrote:

Go through this step by step. Don't take any "shortcuts".

1. Change your formula in D2: =C2-B2
(there is no need for the SUM function)

2. Select ONLY cell D2. Do NOT select the entire column. It will slow down
your worksheet considerably.

3. From the top menu bar select Format/Conditional Formatting.

4. Where it says Condition 1, click the drop down box and select Formula Is:

5. Enter the following formula into the box to the right of where it says
"Formula Is:"

=OR(AND($A2="Yes",($C2-$B2)0,($C2-$B2)<=6),AND($A2="no",($C2-$B2)0,($C2-$B2)<=3))

You can type this in, or copy it from this post and paste it in.

6. Select Format.
7. Select the kind of format you want. (e.g. go to patterns and select
green).

8. <OK
9. <Add
10. Change Condition 2 to "Formula Is:" the same way you did for Condition 1.

11. Enter this formula into the box for Condition 2:

=OR(AND($A2="Yes",($C2-$B2)6,($C2-$B2)<=9),AND($A2="no",($C2-$B2)3,($C2-$B2)<=9))

12. Select Format.
13. Select the kind of format you want. (e.g. go to patterns and select
orange).

14. <OK
15. <Add
16. Change Condition 3 to "Formula Is:" the same way you did for Condition 1 &
2.

17. Enter this formula into the box for Condition 3:

=AND(OR($A2="yes",$A2="no"),($C2-$B2)9)

18. Select Format
19. Select the kind of format you want. (e.g. go to patterns and select red).

20. <OK
21. <OK

22. With cell D2 still selected:
a. From the top menu bar, select Edit/Copy
b. Select as much of column D as you wish to apply this format, e.g.
D2:D1000.
c. From the top menu bar, select Edit/Paste Special and check the
"Format" box.

--OR--

If you want whole rows to be formatted the same way depending on the contents
of Drivable and Elapsed time, in 22b above, instead of just selecting cells in
column D, select your entire table (with enough extra lines to allow for
filling in the blanks); perhaps A2:D1000.

Then Edit/Paste Special and select Formats.

If you do this latter operation, you will need to reformat columns B and C so
that they are formatted as Dates.


--------------------------------------------

On Sun, 13 Nov 2005 05:13:02 -0800, Stuart
wrote:

You asked me the following questions:

Did Excel crash? no

Did you get some error message? no

Something else? yes
What cell contains the conditional formatting? i clicked on the column
heading D so it highlighted every cell in that column which is where i am
putting the conditional formating.

What, exactly (copy/paste the formula please) is in each condition in the
dialog box? in the first condition i have cell value is between 0 and 5
in the second condition i have cell value is between 6 and 9
in the third condition i have cell value is greater than 9

What cells did you use for "Drivable" and "ET" and what, exactly, is
contained in those cells? the word drivable is in cell a1 each row of excel
would refer to a different vehicle so a2 has a value of yes as does a3 and a4
a5 a6 and a7 have values of no. in order to test the new formulae works i
need a record for each test so i needed 3 drivable and 3 not drivable column
b contains the date of notification for each record and column c the date
each vehicle came onsite. the formulae for cell d2 is "=sum(c2-b2)" and then
copied formulae down to d7 so it adapts as it goes down. so in order the
result values are as follows. 5, 6, 10, 2, 3 and 10.


"Ron Rosenfeld" wrote:

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


--ron