Re: [libreoffice-users] Is This Possible In Calc?

At 16:14 07/02/2015 +0100, Dave Barton wrote:
> 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


--
To unsubscribe e-mail to: users+unsubscribe@global.libreoffice.org
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted