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.

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/comm...lic.excel.misc

You got it.

Office 12 will have unlimited conditional formats.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Stuart" wrote in message
...
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.

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow
this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.


http://www.microsoft.com/office/comm...lic.excel.misc

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 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

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

i didnt know how to adap the formulae to work. i am guessing once we fix the
formulae i paste it in to the conditional formating box that has the desired
style for condition being true right?

ok heres how it works. column A is drivable so conditional formating needs
to look at column A and the corresponding cell in column D (Elapsed) so on
row 2 its comparing A2 and D2 to see if they meet the conditon now obviously
i cant use abslute cell referencing so how will this formulae work? in
elapsed itself (Column D) is the formulae =sum(C2-B2) this is used to
calculate how long its been since date of notification to time vehicle came
onsite if this formulae generates a any of the values i specified in my last
post conditional formating needs to take the appropriate action. now you said
a new version of excel wont fix things what i am asking for in office 12 is
that microsoft make some enhancements to the conditional formating tool so it
has unlimited conditions and all i need to do is go and select cell value is
between 0 and 5 click the and button and Column A is equl to drivable. thats
just an example as in the version i want created the dropdown containing cell
value is and formulae is would also include Column A Is Column B is and so on
for every column in excel that contains data. of course if you had selected
an entire row as opposed to an entire column it would replace the would
column with the would row if you get what i mean. this is merely a suggestion
for an improvement to the existing system for implementation in office 12 but
if it can be done in office 2003 please tell me how.

"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

i guess you prefer writing all in one long paragraph with minimal punctuation
or capitalization than organizing in an outline form. i find that very
difficult both to understand and to follow along but since i guess you prefer
that i will respond the same way. what you need to do is substitute for
drivable the cell reference where you have that information and since i think
you will be formatting the same cell in which you have the elapsed time you
have to substitute the formula for elapsed time where i wrote ET. after you
enter the formula for condition 1 click on format and select the format you
want for that condition then hit ok until you are back at the dialog box and
select add to add condition 2. do the same thing there and things should work
ok. since i think you will be entering the same formula in a single column, but
only conditionally formatting a single cell in each row you should enter the
cell references as relative references. then when you copy/drag the formula
down, the references will change to reflect the appropriate row. if you are
going to copy the conditional formatting to different columns, so that a whole
row could be formatted based on the conditions set out, then you should use a
reference of the form $A2 and =sum($C2-$B2) so that you can format an entire
row based on those conditions.

so far as what you want microsoft to do it sounds as if you want a change in
the interface perhaps a conditional formatting wizard of some sort. maybe that
will happen some day but it is not present to the best of my knowledge in v12.

i will be interested if you find the above technique of writing which mimics
your response style easier to comprehend than my initial response in which i
tried to use paragraphs and capitalizations and shorter sentences.

best,
--ron

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

i didnt know how to adap the formulae to work. i am guessing once we fix the
formulae i paste it in to the conditional formating box that has the desired
style for condition being true right?

ok heres how it works. column A is drivable so conditional formating needs
to look at column A and the corresponding cell in column D (Elapsed) so on
row 2 its comparing A2 and D2 to see if they meet the conditon now obviously
i cant use abslute cell referencing so how will this formulae work? in
elapsed itself (Column D) is the formulae =sum(C2-B2) this is used to
calculate how long its been since date of notification to time vehicle came
onsite if this formulae generates a any of the values i specified in my last
post conditional formating needs to take the appropriate action. now you said
a new version of excel wont fix things what i am asking for in office 12 is
that microsoft make some enhancements to the conditional formating tool so it
has unlimited conditions and all i need to do is go and select cell value is
between 0 and 5 click the and button and Column A is equl to drivable. thats
just an example as in the version i want created the dropdown containing cell
value is and formulae is would also include Column A Is Column B is and so on
for every column in excel that contains data. of course if you had selected
an entire row as opposed to an entire column it would replace the would
column with the would row if you get what i mean. this is merely a suggestion
for an improvement to the existing system for implementation in office 12 but
if it can be done in office 2003 please tell me how.

"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

hi there

i still dont understand it so i just give up and will hold out for yes as
you put it a conditional formatting wizard. it may not exist in any beta of
12 currently written but the good thing is if i suggest it which i am trying
to do currently there is still time before the public beta in 2006 to get
such a feature written into office 12 and if not v12 they might add it into
v13 for 2007. sorry for wasting your time as clearly i am not intelligent
enough to understand what to do to make my desired technique work. by the way
i do prefer your original style of writing as i found it easier to read
however the reason i write the way i do is i have never been able to learn
how to structure a paragraph or use grammer correctly as nobody has taken
enough time to help me learn it i am gradually getting better and the grammer
check in word helps although it still doesnt stop me from writing long
sentances so until microsoft perfect that technology i guess im stuck using
my diificult to read method.

"Ron Rosenfeld" wrote:

i guess you prefer writing all in one long paragraph with minimal punctuation
or capitalization than organizing in an outline form. i find that very
difficult both to understand and to follow along but since i guess you prefer
that i will respond the same way. what you need to do is substitute for
drivable the cell reference where you have that information and since i think
you will be formatting the same cell in which you have the elapsed time you
have to substitute the formula for elapsed time where i wrote ET. after you
enter the formula for condition 1 click on format and select the format you
want for that condition then hit ok until you are back at the dialog box and
select add to add condition 2. do the same thing there and things should work
ok. since i think you will be entering the same formula in a single column, but
only conditionally formatting a single cell in each row you should enter the
cell references as relative references. then when you copy/drag the formula
down, the references will change to reflect the appropriate row. if you are
going to copy the conditional formatting to different columns, so that a whole
row could be formatted based on the conditions set out, then you should use a
reference of the form $A2 and =sum($C2-$B2) so that you can format an entire
row based on those conditions.

so far as what you want microsoft to do it sounds as if you want a change in
the interface perhaps a conditional formatting wizard of some sort. maybe that
will happen some day but it is not present to the best of my knowledge in v12.

i will be interested if you find the above technique of writing which mimics
your response style easier to comprehend than my initial response in which i
tried to use paragraphs and capitalizations and shorter sentences.

best,
--ron

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

i didnt know how to adap the formulae to work. i am guessing once we fix the
formulae i paste it in to the conditional formating box that has the desired
style for condition being true right?

ok heres how it works. column A is drivable so conditional formating needs
to look at column A and the corresponding cell in column D (Elapsed) so on
row 2 its comparing A2 and D2 to see if they meet the conditon now obviously
i cant use abslute cell referencing so how will this formulae work? in
elapsed itself (Column D) is the formulae =sum(C2-B2) this is used to
calculate how long its been since date of notification to time vehicle came
onsite if this formulae generates a any of the values i specified in my last
post conditional formating needs to take the appropriate action. now you said
a new version of excel wont fix things what i am asking for in office 12 is
that microsoft make some enhancements to the conditional formating tool so it
has unlimited conditions and all i need to do is go and select cell value is
between 0 and 5 click the and button and Column A is equl to drivable. thats
just an example as in the version i want created the dropdown containing cell
value is and formulae is would also include Column A Is Column B is and so on
for every column in excel that contains data. of course if you had selected
an entire row as opposed to an entire column it would replace the would
column with the would row if you get what i mean. this is merely a suggestion
for an improvement to the existing system for implementation in office 12 but
if it can be done in office 2003 please tell me how.

"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

For me, a big advantage of writing in paragraphs, and using more of an outline
type, is that it helps me structure my thoughts more clearly. I can more easily
see the path I am taking, and that makes it easier to tell if I have gone awry.

Without proper organization, doing things in Excel will be quite frustrating.

If you go back to the first series of questions I asked you (in my second
message in this thread) and reply to them, we may be able to set something up.




On Sun, 13 Nov 2005 01:45:03 -0800, Stuart
wrote:

hi there

i still dont understand it so i just give up and will hold out for yes as
you put it a conditional formatting wizard. it may not exist in any beta of
12 currently written but the good thing is if i suggest it which i am trying
to do currently there is still time before the public beta in 2006 to get
such a feature written into office 12 and if not v12 they might add it into
v13 for 2007. sorry for wasting your time as clearly i am not intelligent
enough to understand what to do to make my desired technique work. by the way
i do prefer your original style of writing as i found it easier to read
however the reason i write the way i do is i have never been able to learn
how to structure a paragraph or use grammer correctly as nobody has taken
enough time to help me learn it i am gradually getting better and the grammer
check in word helps although it still doesnt stop me from writing long
sentances so until microsoft perfect that technology i guess im stuck using
my diificult to read method.

"Ron Rosenfeld" wrote:

i guess you prefer writing all in one long paragraph with minimal punctuation
or capitalization than organizing in an outline form. i find that very
difficult both to understand and to follow along but since i guess you prefer
that i will respond the same way. what you need to do is substitute for
drivable the cell reference where you have that information and since i think
you will be formatting the same cell in which you have the elapsed time you
have to substitute the formula for elapsed time where i wrote ET. after you
enter the formula for condition 1 click on format and select the format you
want for that condition then hit ok until you are back at the dialog box and
select add to add condition 2. do the same thing there and things should work
ok. since i think you will be entering the same formula in a single column, but
only conditionally formatting a single cell in each row you should enter the
cell references as relative references. then when you copy/drag the formula
down, the references will change to reflect the appropriate row. if you are
going to copy the conditional formatting to different columns, so that a whole
row could be formatted based on the conditions set out, then you should use a
reference of the form $A2 and =sum($C2-$B2) so that you can format an entire
row based on those conditions.

so far as what you want microsoft to do it sounds as if you want a change in
the interface perhaps a conditional formatting wizard of some sort. maybe that
will happen some day but it is not present to the best of my knowledge in v12.

i will be interested if you find the above technique of writing which mimics
your response style easier to comprehend than my initial response in which i
tried to use paragraphs and capitalizations and shorter sentences.

best,
--ron

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

i didnt know how to adap the formulae to work. i am guessing once we fix the
formulae i paste it in to the conditional formating box that has the desired
style for condition being true right?

ok heres how it works. column A is drivable so conditional formating needs
to look at column A and the corresponding cell in column D (Elapsed) so on
row 2 its comparing A2 and D2 to see if they meet the conditon now obviously
i cant use abslute cell referencing so how will this formulae work? in
elapsed itself (Column D) is the formulae =sum(C2-B2) this is used to
calculate how long its been since date of notification to time vehicle came
onsite if this formulae generates a any of the values i specified in my last
post conditional formating needs to take the appropriate action. now you said
a new version of excel wont fix things what i am asking for in office 12 is
that microsoft make some enhancements to the conditional formating tool so it
has unlimited conditions and all i need to do is go and select cell value is
between 0 and 5 click the and button and Column A is equl to drivable. thats
just an example as in the version i want created the dropdown containing cell
value is and formulae is would also include Column A Is Column B is and so on
for every column in excel that contains data. of course if you had selected
an entire row as opposed to an entire column it would replace the would
column with the would row if you get what i mean. this is merely a suggestion
for an improvement to the existing system for implementation in office 12 but
if it can be done in office 2003 please tell me how.

"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


--ron

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

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

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

actually it was an error in one of the conditions that caused my problem but
i have fixed it now thanks for all your help ron its greatly appreciated.

"Stuart" wrote:

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

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

actually it was an error in one of the conditions that caused my problem but
i have fixed it now thanks for all your help ron its greatly appreciated.

I'm glad you got it working. Thanks for the feedback.
--ron