Is This Possible In Calc?

users
#1

I don't believe the following is possible, but I hope some Calc guru
(Brian?) here can prove me wrong.

To have a formula in cell A which evaluates the contents of cell B and
changes the style, but NOT the content, of cell C.
eg. A1=contains the formula, B1=0, C1=20
Pseudo Code for cell A1: =IF(B1=10;C1=STYLE(whatever);"")
Changing B1 from 0 to 10 would result in the content of C1 still being
20 with the "whatever" style.

Any thoughts or pointers would be welcome.

Regards
Dave

#2

Format / Conditional formatting.....

This can put a 'conditional format' on cell C depending on a formula.....

#3

Dave,

I think you are looking for conditional formatting (FORMAT>>CONDITIONAL FORMATTING). Try it, you may need to modify your scenario to get the exact results you want. I have not used it recently.

#4

I don't believe the following is possible, but I hope some Calc guru (Brian?) here can prove me wrong.

I'm not sure I'm a guru, but here goes:

To have a formula in cell A which evaluates the contents of cell B and changes the style, but NOT the content, of cell C.
eg. A1=contains the formula, B1=0, C1=20
Pseudo Code for cell A1: =IF(B1=10;C1=STYLE(whatever);"")
Changing B1 from 0 to 10 would result in the content of C1 still being 20 with the "whatever" style.

I think this is potentially easier than you are seeing it. First, you don't need the separate formula in A1, since the STYLE() function affects the cell in whose formula it appears, whilst evaluating to zero - so it can be added to any numerical value there without upsetting it. So the relevant formula needs to go, in your example, into C1.

I can see two ways to do this:
=20+STYLE(IF(B1=10;"Red";"Default"))
or
=20+IF(B1=10;STYLE("Red");0)

These work differently. The first version sets the style of C1 to Red when B1 becomes 10 but resets it to Default if B1 is later changed to some other value. The second version leaves the style set at Red as soon as this first happens and ignores any later changes to B1. I'm guessing you will probably want the former.

Note that I've included your "20" here as if it were a simple numeric value, but it could equally be a formula evaluating to a number.

If the value in C1 is text (whether constant or formula), things get a little more complicated. First, you cannot add the (numeric, zero) result of the STYLE() formula to a text value. You can concatenate the result of STYLE(), but this would result in an explicit "0" being appended to your text value in the cell. You need a way to convert the zero value to a null string, and this is provided conveniently by the T() function. This leads to:
="Some Text"&T(STYLE(IF(B1=10;"Red";"Default")))

Alternatively, you could use conditional formatting applied to C1 with the formula:
B1=10.

I trust this helps.

Brian Barker

#5

Den 07.02.2015 16:14, Dave Barton skreiv:

I don't believe the following is possible, but I hope some Calc guru
(Brian?) here can prove me wrong.

To have a formula in cell A which evaluates the contents of cell B and
changes the style, but NOT the content, of cell C.
eg. A1=contains the formula, B1=0, C1=20
Pseudo Code for cell A1: =IF(B1=10;C1=STYLE(whatever);"")
Changing B1 from 0 to 10 would result in the content of C1 still being
20 with the "whatever" style.

Use conditional formatting.
While in A1, or better in B1 or C1 I think, select *Format -> Conditional Formatting -> **Condition*.
In the dialog window select "Formula is" in the drop down list. In the text field write $B1=10
In the range field write C1.
In the drop down list (Apply Style) select a suitable style, or select New Style to create a new style.
Press OK and all is done.
Kolbjoern

#6

Not from other but you can do it directly in C20.

C20: =formula + IF(B1=10;STYLE(whatever);0)

LibreOffice help: STYLE function
<https://help.libreoffice.org/Calc/Spreadsheet_Functions#STYLE>

#7

First a big thank you to all who took the time to answer.

I started out (many hours ago) trying unsuccessfully to work out a
solution with conditional formatting, which is why I changed direction
to the IF formula in cell A1.

The real world situation is way more complex than I previously
explained, but thanks to all your pointers I have conditional formatting
working correctly and should now be able to work out the rest for myself.

Brian: In my book, you definitely do qualify.

Best Regards
Dave

#8

hmmmmz, my email is broken.... ;(
i still do not see the post from Tom :wink:

#9

Hi :slight_smile:
I like it when a few different people all give basically the same
answer but say it in different ways and with different levels of
detail. It gives more confidence in the answer. Different people
respond better to different styles of answer.

I vaguely remember this question being asked about 4 years ago and
getting no useful answer. So i feel that this mailing list is working
well. My main reason for joining the list was to learn without
spending time struggling through documentation as i tend to have
trouble concentrating for long enough. The threads here show me the
fashionable or popular things that people try to do and help me
if/when i need to do those things. Hopefully that helps me help my
colleagues more easily.

Many thanks for all these answers. I hope they are helping the o.p. too! :wink:
Regards from
Tom :slight_smile: