enhanced conditional formatting

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/community/en-us/default.mspx?mid=78a37de6-80c7-4f77-989b-5f1e77d04d35&dg=microsoft.public.excel.misc
0
Stuart (76)
11/12/2005 11:55:02 AM
excel.misc 78881 articles. 5 followers. Follow

13 Replies
382 Views

Similar Articles

[PageSpeed] 52

You got it.

Office 12 will have unlimited conditional formats.

-- 

HTH

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


"Stuart" <Stuart@discussions.microsoft.com> wrote in message
news:78A37DE6-80C7-4F77-989B-5F1E77D04D35@microsoft.com...
> 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/community/en-us/default.mspx?mid=78a37de6-80c7-4f77-989b-5f1e77d04d35&dg=microsoft.public.excel.misc


0
bob.phillips1 (6510)
11/12/2005 11:59:45 AM
On Sat, 12 Nov 2005 03:55:02 -0800, Stuart <Stuart@discussions.microsoft.com>
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",ET>0,ET<=6),AND(Drivable="no",ET>0,ET<3))

Condition 2 Formula Is:

=OR(AND(Drivable="yes",ET>6,ET<9),AND(Drivable="no",ET>3,ET<9))

Condition 3 Formula Is:

=AND(OR(Drivable="yes",Drivable="no"),ET>9)


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
0
ronrosenfeld (3123)
11/12/2005 1:04:35 PM
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 <Stuart@discussions.microsoft.com>
> 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",ET>0,ET<=6),AND(Drivable="no",ET>0,ET<3))
> 
> Condition 2 Formula Is:
> 
> =OR(AND(Drivable="yes",ET>6,ET<9),AND(Drivable="no",ET>3,ET<9))
> 
> Condition 3 Formula Is:
> 
> =AND(OR(Drivable="yes",Drivable="no"),ET>9)
> 
> 
> 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
> 
0
Stuart (76)
11/12/2005 5:11:03 PM
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 <Stuart@discussions.microsoft.com>
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 <Stuart@discussions.microsoft.com>
>> 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",ET>0,ET<=6),AND(Drivable="no",ET>0,ET<3))
>> 
>> Condition 2 Formula Is:
>> 
>> =OR(AND(Drivable="yes",ET>6,ET<9),AND(Drivable="no",ET>3,ET<9))
>> 
>> Condition 3 Formula Is:
>> 
>> =AND(OR(Drivable="yes",Drivable="no"),ET>9)
>> 
>> 
>> 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
0
ronrosenfeld (3123)
11/12/2005 6:20:53 PM
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 <Stuart@discussions.microsoft.com>
> 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 <Stuart@discussions.microsoft.com>
> >> 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",ET>0,ET<=6),AND(Drivable="no",ET>0,ET<3))
> >> 
> >> Condition 2 Formula Is:
> >> 
> >> =OR(AND(Drivable="yes",ET>6,ET<9),AND(Drivable="no",ET>3,ET<9))
> >> 
> >> Condition 3 Formula Is:
> >> 
> >> =AND(OR(Drivable="yes",Drivable="no"),ET>9)
> >> 
> >> 
> >> 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
> 
0
Stuart (76)
11/12/2005 8:24:03 PM
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 <Stuart@discussions.microsoft.com>
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 <Stuart@discussions.microsoft.com>
>> 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 <Stuart@discussions.microsoft.com>
>> >> 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",ET>0,ET<=6),AND(Drivable="no",ET>0,ET<3))
>> >> 
>> >> Condition 2 Formula Is:
>> >> 
>> >> =OR(AND(Drivable="yes",ET>6,ET<9),AND(Drivable="no",ET>3,ET<9))
>> >> 
>> >> Condition 3 Formula Is:
>> >> 
>> >> =AND(OR(Drivable="yes",Drivable="no"),ET>9)
>> >> 
>> >> 
>> >> 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
0
ronrosenfeld (3123)
11/12/2005 9:15:07 PM
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 <Stuart@discussions.microsoft.com>
> 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 <Stuart@discussions.microsoft.com>
> >> 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 <Stuart@discussions.microsoft.com>
> >> >> 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",ET>0,ET<=6),AND(Drivable="no",ET>0,ET<3))
> >> >> 
> >> >> Condition 2 Formula Is:
> >> >> 
> >> >> =OR(AND(Drivable="yes",ET>6,ET<9),AND(Drivable="no",ET>3,ET<9))
> >> >> 
> >> >> Condition 3 Formula Is:
> >> >> 
> >> >> =AND(OR(Drivable="yes",Drivable="no"),ET>9)
> >> >> 
> >> >> 
> >> >> 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
> 
0
Stuart (76)
11/13/2005 9:45:03 AM
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 <Stuart@discussions.microsoft.com>
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 <Stuart@discussions.microsoft.com>
>> 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 <Stuart@discussions.microsoft.com>
>> >> 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 <Stuart@discussions.microsoft.com>
>> >> >> 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",ET>0,ET<=6),AND(Drivable="no",ET>0,ET<3))
>> >> >> 
>> >> >> Condition 2 Formula Is:
>> >> >> 
>> >> >> =OR(AND(Drivable="yes",ET>6,ET<9),AND(Drivable="no",ET>3,ET<9))
>> >> >> 
>> >> >> Condition 3 Formula Is:
>> >> >> 
>> >> >> =AND(OR(Drivable="yes",Drivable="no"),ET>9)
>> >> >> 
>> >> >> 
>> >> >> 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
0
ronrosenfeld (3123)
11/13/2005 12:11:22 PM
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 <Stuart@discussions.microsoft.com>
> 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 <Stuart@discussions.microsoft.com>
> >> 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",ET>0,ET<=6),AND(Drivable="no",ET>0,ET<3))
> >> 
> >> Condition 2 Formula Is:
> >> 
> >> =OR(AND(Drivable="yes",ET>6,ET<9),AND(Drivable="no",ET>3,ET<9))
> >> 
> >> Condition 3 Formula Is:
> >> 
> >> =AND(OR(Drivable="yes",Drivable="no"),ET>9)
> >> 
> >> 
> >> 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
> 
0
Stuart (76)
11/13/2005 1:13:02 PM
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 <Stuart@discussions.microsoft.com>
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 <Stuart@discussions.microsoft.com>
>> 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 <Stuart@discussions.microsoft.com>
>> >> 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",ET>0,ET<=6),AND(Drivable="no",ET>0,ET<3))
>> >> 
>> >> Condition 2 Formula Is:
>> >> 
>> >> =OR(AND(Drivable="yes",ET>6,ET<9),AND(Drivable="no",ET>3,ET<9))
>> >> 
>> >> Condition 3 Formula Is:
>> >> 
>> >> =AND(OR(Drivable="yes",Drivable="no"),ET>9)
>> >> 
>> >> 
>> >> 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
0
ronrosenfeld (3123)
11/13/2005 1:53:58 PM
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 <Stuart@discussions.microsoft.com>
> 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 <Stuart@discussions.microsoft.com>
> >> 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 <Stuart@discussions.microsoft.com>
> >> >> 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",ET>0,ET<=6),AND(Drivable="no",ET>0,ET<3))
> >> >> 
> >> >> Condition 2 Formula Is:
> >> >> 
> >> >> =OR(AND(Drivable="yes",ET>6,ET<9),AND(Drivable="no",ET>3,ET<9))
> >> >> 
> >> >> Condition 3 Formula Is:
> >> >> 
> >> >> =AND(OR(Drivable="yes",Drivable="no"),ET>9)
> >> >> 
> >> >> 
> >> >> 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
> 
0
Stuart (76)
11/13/2005 4:01:01 PM
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 <Stuart@discussions.microsoft.com>
> > 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 <Stuart@discussions.microsoft.com>
> > >> 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 <Stuart@discussions.microsoft.com>
> > >> >> 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",ET>0,ET<=6),AND(Drivable="no",ET>0,ET<3))
> > >> >> 
> > >> >> Condition 2 Formula Is:
> > >> >> 
> > >> >> =OR(AND(Drivable="yes",ET>6,ET<9),AND(Drivable="no",ET>3,ET<9))
> > >> >> 
> > >> >> Condition 3 Formula Is:
> > >> >> 
> > >> >> =AND(OR(Drivable="yes",Drivable="no"),ET>9)
> > >> >> 
> > >> >> 
> > >> >> 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
> > 
0
Stuart (76)
11/13/2005 6:09:02 PM
On Sun, 13 Nov 2005 10:09:02 -0800, Stuart <Stuart@discussions.microsoft.com>
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
0
ronrosenfeld (3123)
11/13/2005 7:20:46 PM
Reply:

Similar Artilces:

Formatting changes when documents are emailed
I sometimes send Word documents to a friend. He uses Word 2003, I have 2000. The documents are relatively straightforward, using a single style and an uncomplicated layout, with only two sections, the second of which has different margin and tab settings to the first, and also page numbering. If my friend prints a document directly from the email attachment, my formatting is reproduced exactly. If, though, he saves the attached file to his hard drive and then prints it from there, the formatting goes awry. Specifically, the section break is ignored and the section two layout,...

Crystal Reports Enhancement Problem
I listened to a user (1st mistake) who said Crystal Reports 8.5 wasn't included with CRM and that we needed it so he could modify reports. I installed crystal reports 8.5 Pro nly to find out that a Pre-Configured version of Crystal 8.5 needs to be utilized, now i can not access any reports. I get a 'Crystal Report Logon Error. Please verify that you have enough Crystal Licenses' Any ideas on how to rectify this problem and where is this Pre- Configured version? Thanks, VLE. VLE, As you've discovered, you can't install the Crystal IDE on the CRM Server as i...

Conditional formatting #64
I need to apply conditional formatting to a pivot chart that needs to reflect drops in revenue for consecutive months. The chart is setup as follows. Jan-04 Feb-04 Mar-04 April-04 etc. collections 65,333 65111 64333 59000 I need to have the colors change on the month that collections drops for the 1st time, and then change to a different color if it reaches a decline for two consecutive months and again change to red when collections has declined for three + consecutive months, and of course remain uncolored if does not meet one of these criteria. An...

PO Enhancement
Hello, Hope someone can help me on this topic :) We have this customer still on GP8.0 and having issues with POE. There is an issue when entering commitments against accounts for a budget year. Typically, if the amount being entered is exceeding the allowed budget, the over budget message below appears and the commitment cannot be printed but can still be saved. "The item has exceeded the budget for account number XXX-XXXX-XX by $XXXX. The budget amount for this account is $XX. Do you want to authorize this amount? " However, there are some instances for some accoun...

Problem formatting for printing
I'm trying to format an Excel spreadsheet to print; either by selecting the section I want to print and/or using the view/ page break preview. For whatever reason it is not alloweing me to reduce the percentage size to fit on one page --or drag the page borders in the page break preview. The spreadsheet is columns A through O and rows 1 through 58. Its strange-- here's the error message it gives This error can appear if you have attempted to print content which cannot fit on the printable area of the selected printer. Usually this occurs when you have selected to: Print to ...

Conditional Formatting and Macros in Excel
While I'm not new to Excel, I am new to using conditional formatting, functions and macros within Excel. I'm working on a sheet that tracks certain data on new hires. We track this data for 3 consecutive 30 day periods. I want to create two macros that will make things easier for me to report data in this worksheet. One of the macros I would like to put in is any time I put in a new hire's info it will go ahead and create a new line with all of the appropriate formatting for each column (several of the columns will have conditional formatting). This would be similar to how ...

What format do I use for a witness statement
What format do I use for a witness statement "What format for a witness statement" <What format for a witness statement@discussions.microsoft.com> wrote in message news:783AC228-B07C-4308-8D26-5D696E213CBF@microsoft.com... > What format do I use for a witness statement Whatever the court in your area requires. See a lawyer for that information or contact the court. We can't help you since all states and countries have different laws and procedures. "What format for a witness statement" <What format for a witness statement@discussio...

Unable to format cells
In my Excel 2003 application, I select a range of cells and set a vertical border. This also works in Excel 2007 until I save it from the latter into the compatible format. If I then open it again with 2007, it's inoperative. I select the range, right-click, then click on format cells and nothing happens. If I open it again with 2003, it works. If I then save from 2003 and then open it with 2007, it will work. Any ideas? ...

Enhanced MetaFile
Hello I saved a Enhanced Matafile Object in a Access database field as OLE object data type. Is there a way to read the object from C++ and get the handle for EMF Thanks ...

Outlook 2007 formatting tags
> This message is in MIME format. Since your mail reader does not understand this format, some or all of this message may not be legible. --B_3278045361_13711237 Content-type: text/plain; charset="US-ASCII" Content-transfer-encoding: 7bit I use Outlook 2007 and when I create an outlook invite in Calendar; the formatting tags get activated (very annoying). I go to edit options>display and take the check mark off the Formatting tags. Does anyone know how to make this choice to disable formatting tags for good? Thanks, Elias --B_3278045361_13711237 Content-type: text/html...

Enhancement requests
Does anyone know how to file an enhancement request for Money? Write a review for a magazine with a huge circulation. It's not clear anything else is effective. "Emily" <Emily@discussions.microsoft.com> wrote in message news:C631D387-4AD2-4030-AEA9-361440A6F3CF@microsoft.com... > Does anyone know how to file an enhancement request for Money? There is a place to file enhancement requests but I don't remember where it is. I would not bother looking again since I have filed many, many such requests and all of them were ignored. As I have stated in other post...

OR condition in Search Folders
Does anyone know how to use an OR in search folders? I'd like to see any message that is Unread OR Flagged. thanks. you'll need to use QueryBuiler. http://www.outlook-tips.net/archives/2004/20040927.htm -- Diane Poremsky [MVP - Outlook] Author, Teach Yourself Outlook 2003 in 24 Hours Need Help with Common Tasks? http://www.outlook-tips.net/beginner/ Outlook 2007: http://www.slipstick.com/outlook/ol2007/ Outlook Tips by email: dailytips-subscribe-request@lists.outlooktips.net Outlook Tips: http://www.outlook-tips.net/ Outlook & Exchange Solutions Center: http://www.slipstick...

Conditional Formatting
How would I go about making a cell change the text to equal D/O if the cell is equal to "d". I can use conditional formatting to make the text white and the fill purple but I can't get the formula to change the text from "d" to D/O. Any ideas? I currently have this furmula under the conditional formatting: =IF(C4="d",1, 0) Thanks hi conditional formating cannot change the data in the cell, only the formating. an if formula in the call can to that but not CF. if a formula wont work for you then you may be looking at an event macro. Rega...

Formatting font and color of worksheet tabs
I've got a workbook with about 60 tabs, and I'd like to color code th tabs for easier navigating. I've seen workbooks before where someon change the actual color of the tab, but I can't figure out how to d it. BTW, I'm using Excel 200 -- rbank ----------------------------------------------------------------------- rbanks's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=294 View this thread: http://www.excelforum.com/showthread.php?threadid=27282 Hi this feature was not introduced until excel 2002 - AFAIK there is no way of doing it in 20...

Conditional format formula
How can I enter a conditional formula so the cell color changes based on odd or even numbers? Ex: even numbers - cell color is white, odd numbers - cell colors are grey If I understand what you're asking, you *only* want the cell to be gray when it contains an odd number, since it's *normally* white anyway. <Format> <ConditionalFormat>, Click "Formula Is", and enter this: =MOD(A1,2)<>0 Then select your format and <OK>. -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within ...

SDK for Purchase Order Enhancement
Most third party products have SDK written by the Development Team, the partner needs to know what parameters available which can be used for integration with other third party products. ...

The latest Media Player: can the Enhancements panel be made permanently visible?
I've noticed two small but significant differences between the Media Player that comes with Win7 and the XP one I was used to (I'm sure there are more differences, but these are the two which affect my everyday use). Firstly, in the old player, you could select to have the "Enhancements" control panel permanently displayed (I kept it to the Video Settings option with the brightness and contrast controls). In the new version I have to call up the controls again every time the program is run. Is there an "always visible" option? Secondly, when I p...

Conditional formatting 11-26-07
I would like to hide/show a line based on the value of a control in a continuous form. Conditional formatting is only enabled for text boxes. So how can that be done? Technically, you cannot selectively hide/show a control on a form in Continuous view as the FormatConditions object does not expose a "Visible" property. In your specific case, a possible workaround is to use the Background color of the standard TextBox control, set to the desired line color, sized vertically to simulate a line of the desired height. Now you can apply Conditional Formatting to the control, sett...

Condtional Formatting
My conditional formatting applies to cells (see row numbers): =$B$41 =$B$42 When I insert 10 rows between row 41 and 42, the condition now applies to cells as follows: =$B$41:$B$51 1. Is there a way that the condtions will not be copied to rows 42 to 51? Or, the condition will apply only to each cell, i.e." =$B$41 =$B$42 =$B$43 etc. to =$B$51 Thank you. Use the absolute column reference of $B and the relative row reference of 41 to get the seconf optiont. =$B41 To not have the CF copied to the inserted rows, turn off the option to "exte...

Can't format font colors or cell fill-in colors
I cannot fill-in a cell with a color or format the color of the font. I verified that this I can change font color in Word. So it appears to only be happening in Excel. Already tried relaunching and rebooting computer. Anyother ideas or places to look where I may have changed a setting? -- rrl Does this happen in all workbooks, or just a single workbook? -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "canoeron" <canoeron@discussions.microsoft.com> wrote in message news:CB278F8E-EF1F-4218-91F8-C100F1D30256@micros...

2 Portal Enhancements
1. If the user is not yet set up (or was set up incorrectly) in Business Portal - provide a better error message than "An unexpected error has occurred" when they try to navigate to the site. This does happen and causes confusion. I have seen it twice at 2 different customers - once when a new employee came on board and the administrator forgot to add them to the Portal site and another time when the wrong logon was used to set up the user in Portal. 2. Please improve the way that you can create, copy, share queries. The current method is very cumbersome - 2 Microsoft support...

Formatting a formula
This is probably not possible, but I figure there's not harm in asking. I know that if I have a cell containing "apples and oranges" and I highlight "apples" and click the bold button, only "apples" will be in bold face and "oranges" will stay regular. Is it possible to do this with a formula. What I mean is this: A1="apples " A2="and oranges" A3=A1&A2 Is there any way that in A3 only "apples" be in bold face? Alex Not by using a formula. Gord Dibben MS Excel MVP On Sat, 12 Dec 2009 16:...

Conditional formatting capability for 20+ conditions not just 3.
I have a spreadsheet that I use to schedule workers. This spreadsheet is setup with conditional formatting for 3 of the positions. However, I have to manually go thru and change the formatting for the others which at times can be over 20 positions. To be able to assign conditional formatting for all possiblilties in the same cell would be great. Example cell contains schedule tasks indicated by letter codes. "I" would display Green background "R" would display Light Blue background "V" would display Medium Blue background "N" would display Yellow...

Help! Chart losing date format
Help! Excel Charts losing formats I am using Excel 2000 SP-3 on a Windows 2000 machine and have bee having some strange problems. I have 4 charts that are in a singl workbook along with the source data for them. After the workbook ha been updated and the charts are correct, I am copying the charts to brand new workbook by right clicking the worksheet tab and selectin copy option. I am only copying the chart to the new workbook, not th data. Two of the charts copy fine but the other two charts are changin from having a gray background to an orange background. I am currentl manually having to ...

Paste Special/Format
Whenever I try to paste cell A's format into cell B, the value & format of A gets pasted into B. The same result happens when I use the paint brush application to just paste the format into another cell. Any ideas? What version of Excel are you using?......any macros involved? Vaya con Dios, Chuck, CABGx3 "Kamron" <Kamron@discussions.microsoft.com> wrote in message news:2A1C8192-74E6-4597-ABA7-22C173170B54@microsoft.com... > Whenever I try to paste cell A's format into cell B, the value & format > of A > gets pasted into...