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