2009/11/7 Walter Hildebrandt <wh2...@gmail.com>:
> I created a test spreadsheet and put 50 in cell A1, 30 in cell A2, and 40 in
> cell A3. Putting the formula
>
> A1-40+IF(A1>40;STYLE("RedText");0)
>
> in cell B1. I then copied B1 to B2 and to B3.
>
> The correct numbers are in B1, B2, and B3. B1 is 10, B2 is -10 and B3 is
> 0. *The problem is that all the B cells have red numbers.* I have probably
> not crated the correct RedText style
I tried it when I replied to you and it worked. I am not sure why it
doesn't for you.
Maybe you messed things up when creating the style?
Did you somehow change your default style to display red numbers? F11
→ Right click Default → Click modify → Click Font effects tab. Is the
font colour set to red? If so, change it back to Automatic.
Does it work now?
If not, highlight the cells that contains the style formula. Right
click one of the cells → Format cells… → Font effects. Is the font
colour set to red? Change it back to Automatic.
Does it stil not work? Please send me the whole spreadsheet, or a
similar one that doesn't work, and I will try to debug it for you.
Johnny Rosenberg
>
> When creating the RedText style, I clicked on the “Font Effects” tab > In
> the “Font color” pull-down menu I selected *Red* > I click *OK*. How do I
> get the minus numbers, such as the -10 in B2, to be black instead of red?
>
>
> On Sat, Nov 7, 2009 at 12:34 AM, Johnny Rosenberg
> <gurus.knu...@gmail.com>wrote:
>
>> 2009/11/7 Johnny Rosenberg <gurus.knu...@gmail.com>:
>> > 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).
>>
>> Type this in A1 on the View sheet, of course. Seems like I forgot to
>> mention that…
>>
>> Johnny Rosenberg
>>
>>
>> >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
>>
>>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscr...@openoffice.org
For additional commands, e-mail: users-h...@openoffice.org
