2009/11/6 Walter Hildebrandt <wh2...@gmail.com>:
> Cell A1 has a number.
> Cell B1 is to have how much less, or much more, A1 is than 40.
> When A1 is less than 40 the answer in B1 is to be in black.
> When A1 is more than 40 the answer in B1 is to be in red.
> .
> This means if A1 is 50, the 10 in B1 is red. When A1 is 30, the -10 in B1
> is black.
>
Have you tried the STYLE command?
First create a style with red text, let's call it RedText. If you
don't know how to crate styles, press F11 and experiment a bit from
there. Hint: Right clicking an existing style might be a good start…
Now, in B1, enter your formula:
=A1-40+IF(A1>40;STYLE("RedText");0)
The STYLE function always returns 0, so the formula is mathematically
equal to A1-40+0.
Using conditional formatting will also work. This is just another way
to do the same thing in this case. In some cases conditional
formatting is the better choice, in some cases this solution is the
one to prefer.
A bit OT:
I'll give you an example where the use of STYLE is exceptionally good:
Let's say that you have a cell than can contain some words (maybe
names of people?). Maybe we have a list of when someone will do what.
Maybe we have a certain task to do and we decided that we all would
share the job.
Everyone can take a look on the list to see when he or she is supposed
to do the task.
Well, if every one of us was assigned a specific colour, it would be
easy to see your name in the list, right? So my name maybe will be
displayed on a red background, your with a blue backgound, Richard is
yellow, Gene is green (which is a rhyme, by the way…) and Brian is
Magenta. Well, first we create one style for each person. Give each
style the same name as the person associated with it.
To do this the easy way (maybe there is even easier ways, please tell
me in that case), use one sheet for inputing data and another sheet
for viewing it. Name the sheets, maybe ”Input” and ”View”.
Let's say that we use column A for date and column B for peoples
names. Let's also say the the first row is for headers.
So input a date in A2 and a name in B2 and continue with A3, B3 and so
on. All this is done on the Input sheet, of course.
Now switch to the View sheet.
Copy the headers from the Input Sheet. A good way (I think) to do this
is to just type ”=Input.A1” (without the quotes, of course, like in
the rest of my examples in this message). Now you can copy this cell
where ever you want in the View sheet, and it will display the value
of the corresponding cell on the Input sheet. This way, if you change
your headings or other cells, you only need to change them on the
Input sheet, which is a good thing in most cases.
Ok, now input the following in B2:
=LEFT(Input.B2 & STYLE(Input.B2);LEN(Input.B2))
Highlight B2 (still on the View sheet) and copy it down as far as you need.
Without the LEFT function, Walter would have been turned into Walter0,
for example, and that doesn't look very good…
This way we are not limited to the three conditions of conditional
formatting. If we are 100 people, we ”only” need to create 100 styles
to make it work. The tough thing in this case is maybe to find 100
different colours that doesn't look like each other…
Well, this was only an example and it works (I actually tested it
while I wrote this message).
Johnny Rosenberg
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscr...@openoffice.org
For additional commands, e-mail: users-h...@openoffice.org
