Ubuntu
openoffice.org package

Pick a cell reference and insert it in a different cell including font colour

Asked by Box Linux

I want to use an =IF(1<5;A1;A3) where A1 would have the contents "O.K." (in green) and A3 would have the contents "Wrong" (in red).
If I set the cell format for A1 to green, because I only have font colour automatic in the =IF cell, A1 gets printed in black in the =IF cell. The same also goes for cell A3.

Question information

Language:
English Edit question
Status:
Solved
For:
Ubuntu openoffice.org Edit question
Assignee:
No assignee Edit question
Solved by:
Ralph Janke
Solved:
Last query:
Last reply:
Revision history for this message
Ralph Janke (txwikinger) said : 		#1

Thanks for your question.

Did you try the function "STYLE"?

Quote from the help from OpenOffice:

STYLE
Applies a style to the cell containing the formula. After a set amount of time, another style can be applied. This function always returns the value 0, allowing you to add it to another function without changing the value. Together with the CURRENT function you can apply a color to a cell regardless of the value. For example: =...+STYLE(IF(CURRENT()>3;"red";"green")) applies the style "red" to the cell if the value is greater than 3, otherwise the style "green" is applied. Both cell formats have to be defined beforehand.

I hope this helps

Revision history for this message
Box Linux (boxlinux) said : 		#2

Hi Ya Ralph, thanks for your feedback.
This is what I tried. Firstly the formula =IF(1<2;A1;A2) This worked picking A! which has OK in it,. Changing the formula to =IF(1>2;A1;A2) Picks A2 which has Wrong in it.
Then I tried =IF(1>2;A1;A2)+=STYLE(IF(CURRENT()<3;"red";"green")) this gave ERR 510.
Then I tried =IF(1>2;A1;A2)+STYLE(IF(CURRENT()<3;"red";"green")) this gave ERR502
I am obviously a bit of a dimwit but if there is any extra help it would be appreciated.
Bye for now

BOX

Revision history for this message
Ralph Janke (txwikinger) said : 		#3

It works for me.

I did the following:

Go into Menu Format->Styles and Formatting then create two new styles with the name red and green (in which I just set the color in the Font Effects Tab).

Then I used your second line =IF(1>2;A1;A2)+STYLE(IF(CURRENT()<3;"red";"green"))

If A2 is 3 or greater it shows it in green, otherwise in red.

The important thing... the names you use in the STYLE function, i.e. red/green must exist as styles.

I hope this helps.

Revision history for this message
Box Linux (boxlinux) said : 		#4

Hi Ralph,
I tried your solution and it only prints the contents of A1 and A2 if
they contain numbers. If they contain text they only show the ERR code
but in the right colour

Thanks for your help

Box

On Tue, 08 May 2007 11:54:28 -0000
Ralph Janke <email address hidden> wrote:

> Your question #6247 on openoffice.org in ubuntu changed:
> https://answers.beta.launchpad.net/ubuntu/+source/openoffice.org/+question/6247
>
> Status: Open => Answered
>
> Ralph Janke proposed the following answer:
> It works for me.
>
> I did the following:
>
> Go into Menu Format->Styles and Formatting then create two new styles
> with the name red and green (in which I just set the color in the Font
> Effects Tab).
>
> Then I used your second line
> =IF(1>2;A1;A2)+STYLE(IF(CURRENT()<3;"red";"green"))
>
> If A2 is 3 or greater it shows it in green, otherwise in red.
>
> The important thing... the names you use in the STYLE function, i.e.
> red/green must exist as styles.
>
> I hope this helps.
>
> _______________________________________________________________________
> If this answers your question, please go to the following page to let
> us know that it is solved:
> https://answers.beta.launchpad.net/ubuntu/+source/openoffice.org/+question/6247/+confirm?answer_id=2
>
> If you still need help, you can reply to this email or go to the
> following page to enter your feedback:
> https://answers.beta.launchpad.net/ubuntu/+source/openoffice.org/+question/6247
>

Revision history for this message
Best Ralph Janke (txwikinger) said : 		#5

Why don't you try something like this:

=IF(2<1;A1;A2);STYLE(IF(EXACT(CURRENT();'OK');"green";"red"))

The condition in the first IF (2<1) should be changed to whatever your test is.

I hope this helps.

Revision history for this message
Box Linux (boxlinux) said : 		#6

Thanks Ralph Janke, that solved my question.

Revision history for this message
Box Linux (boxlinux) said : 		#7

Hi ya Ralph

I can't get it to do what I want so I am going to close this question.

This is starting to make my head hurt.

Best regards

Box

On Wed, 09 May 2007 17:30:18 -0000
Ralph Janke <email address hidden> wrote:

> Your question #6247 on openoffice.org in ubuntu changed:
> https://answers.beta.launchpad.net/ubuntu/+source/openoffice.org/+question/6247
>
> Status: Open => Answered
>
> Ralph Janke proposed the following answer:
> Why don't you try something like this:
>
> =IF(2<1;A1;A2);STYLE(IF(EXACT(CURRENT();'OK');"green";"red"))
>
> The condition in the first IF (2<1) should be changed to whatever your
> test is.
>
> I hope this helps.
>
> _______________________________________________________________________
> If this answers your question, please go to the following page to let
> us know that it is solved:
> https://answers.beta.launchpad.net/ubuntu/+source/openoffice.org/+question/6247/+confirm?answer_id=4
>
> If you still need help, you can reply to this email or go to the
> following page to enter your feedback:
> https://answers.beta.launchpad.net/ubuntu/+source/openoffice.org/+question/6247
>