Overlapping (or double) Conditional formatting

[krembo99]

Hello all,

I have a spreadsheet that should be presented in a multi-currency format .
So that is easy ,I created 3 styles ( for currency formatting ) for 3 different currencies and use 3 simple conditions "is_formula"

Code: Select all

IF($D$2$=1)

then

Code: Select all

IF($D$2$=2)

and

Code: Select all

IF($D$2$=3)

where the currencies codes for the user are simply 1=USD , 2=CNY and 3=ILS. In this way the user just inputs the number of the desired currency and all is ok . ( BTW - if there is a more clever way of doing that - i would like to know )

The problem began when someone asked me to do alternate shadings for the rows . So now , I need to have two overlapping conditions - that seemingly have no relations between them . Usually - for alternate conditions I just put

Code: Select all

ISODD(ROW())

and

Code: Select all

ISEVEN(ROW())

coupled with two styles of shadings .
So, the question is - Is there a way to connect these two conditions in order to have alternate rows shading, AND keep the functionality of the multi-currency , all that without having to create ( at least ) 6 different styles ( USD-DARK, USD-LIGHT .. etc .) . The real problem is that I need to have 7 currencies at the end - so the mentioned method of duplicating Currency-dark/light is not really an option .
I need some smart catch-all formula ..
BTW - I also tried some other formulas like

Code: Select all

AND($D$2=2,ISEVEN(ROW)) ) 

etc . But no help.

Example file attached .

[gooner89]

Just a thought (might work, might not, who knows!), could you not highlight all cells, then hold CTRL and unhighlight the columns or selected cells to have the currency format, then do the conditional format on the remaining cells. Then after, highlight only the currency cells and apply the conditional format for it? That's assuming the currency format takes priority of course

[Zizi64]

krembo99,

Are you using Apache OpenOffice or LibreOffice?

The Libreoffice can hadle more than 3 conditions for a cell or a cellrange.

[Villeroy]

First condition formula: STYLE(INDEX($F$1:$F$3;$D$2;1))

[Zizi64]

Example in LibreOffice:

See the created cell styles on the SideBar, and the six conditions in the CF window.
Note: It will work in LibreOffice only...

test.currency2.ods

(19.9 KiB) Downloaded 152 times

[krembo99]

First of all - thanks a lot for all thee replies and answers - I am always a bit amazed of the kindness and efforts of people in this forum ..

Are you using Apache OpenOffice or LibreOffice?

I am using both. LibreOffice 5.1.3.2 on Win7 and AOO 4.1.x ( not sure ) on UBUNTU.
At my work, all the computers are with AOO, and all my private machines have both AOO and LibreOffice as portables.

The Libreoffice can hadle more than 3 conditions for a cell or a cellrange.

I know that more than 3 conditions can be used - But somehow there are not operators (AND,OR .. ) between them . That makes it more difficult .
See my original file for example.
But regarding this - how exactly does conditions being handled ? Does calc goes over all of them, or if the first condition is satisfied - it skip the next ?
lack of operators really makes it a bit hard to understand .

See the created cell styles on the SideBar, and the six conditions in the CF window.

Your solution indeed works , just like I wrote in the original question ( 6 separated styles ) .
I also wrote that this is what I want to avoid , having 7 or more currencies can be a bit challanging .

First condition formula: STYLE(INDEX($F$1:$F$3;$D$2;1))

Yes ! - Thanks , this seems to be a great way to reduce the 3 primitive functions I have made into 1 - and if fact , When I first applied it - it worked wonderfully .
Well - actually a bit strange But on the right track :

But - when I started to try and apply more conditions - weird display problems started to occur .
I saw flickering of cells and some other problems ( not seeing selections range ).
So I looked up the STYLE() function, which I did not knew before , and saw that it is actually time-dependent , and constantly updating ( and calculating );
Then I tried to remove the time parameter : STYLE(INDEX($F$1:$F$3;$D$2;)) - But same problem .

The second thing I tried , was to apply this formula with a much simpler condition :
Let's take simple two color scale , with formula ISODD(ROW()) and ISEVEN(ROW()) - and voila -

But alas - seems like this condition is not working at all for me ( a hidden bug ?? ) - even when applied ALONE .
( Can see attached file for both bugs - display errors, and not working conditions )

Now, allow me to rant a bit ( And by all means - this is not about the work that people do - which is wonderful. it is more about overall strategy )

The main problem , in a broader view of how OOO handles this issue , as I see it is this :
Styling and Formatting ( Numbers in particular ) are NOT interchangeable terms.
A number FORMAT is the like TYPE of data whereas STYLE is more connected to how to DISPLAY this data .

Thinking in terms of ( simple ) programming , a Number type can be INT, STRING , FLOAT etc . It should not influence the way that number is DISPLAYED .
Continuing in terms of simplified example - that most people can understand , lets take a simple web-app with PHP/HTML/CSS.
In PHP I would cast the TYPE of the number ( not a must , but let's assume I do ) , but the DISPLAY of that number will be handled by HTML / CSS.

In CALC, the "FORMATTING" function is totally wrong in concept .
I assume that this is inherited from the need to "copycat" excel , which in turn inherited this behavior from it's long ( and OLD ) legacy.
( This is another rant I have : the seemingly need / want of OOO/Libre to "Follow" or " Copy" excel - which is already bad in terms of UX/UI - instead of being a leader in design and function - but that is not for this thread )

The right way to handle that issue would be to separate STYLING and FORMATTING . Especially when Numbers are discussed . In fact - the "numbers" tab has no business of being in the "styles" dialog .
The number " FORMAT " or "TYPE" should be a property of the cell, not related to the display style .
If that was really the case , many problems related to Formatting, could have been resolved .

For example , there is no reason why one should use a complicated mechanism of " conditional formatting " to achieve simple alternating background .
Wouldn't it be much more simple to put that is the "background " menu like an option ?

Anyhow, enough with the rants , I am still looking for a solution - so any test / try is very welcomed .

[Zizi64]

You can not combine two type of the conditional format feature (for example: Condition AND ColorScale) of the Calc application in same time on a specific cell or on a specific cellrange. (And you can not combine two set of the conditional format in same time with two independent usage of the CF function on a specific cell or cell range.)
The values of the Color Scale (Red 3 and Blue 3) are NOT Cell styles, but they are pure color names only, therefore you can not determine other properties of the cell by the usage of them.

I know that more than 3 conditions can be used - But somehow there are not operators (AND,OR .. ) between them . That makes it more difficult .

My Opinion: you must use the method with the "primitive conditions" method suggested by me. You can use only one CF function for one cell (or cellrange) but you can use more than 3 conditions (and more than3 Styles) in the LibreOffice Calc. And you can use BOOLEAN operators (AND/OR/NOT) in the conditions.
See my attached .ods file above, again.


Another workaround tip:
...Or you can write your own macro for applying some direct formatting properties for the target cells based on the conditions and cell values descripted in your macro code. This macro must be a SUBROUTINE, and you must launch it manually or by an event like the "Document changed", because the user defined cell FUNCTIONS can not modify other cells, but only the cell/s/ where the function was called from.

[Zizi64]

Third Workaround method:
There is a feature named AutoFormat. You can create your own background pattern, and you can eliminate the other properties applied by this feature.
https://help.libreoffice.org/Calc/Using ... for_Tables

It is a useful feature, but you must be careful, because the applied STYLES can overwrite the cell properties applied by the AutoFormat. Do not set any background color in your own currency cell styles, and make sure if they not inherit such properties from the parent/default cell style.

[erbsenzahl]

There is a German extension for more than 3 CF in OpenOffice: http://www.calc-info.de/files/mottco_102c.oxt on page http://www.calc-info.de/makros.htm
It is easy (?) to check what the German words mean in English. It's worth a try. Macros are to be activated.

[Villeroy]

I wrote a very simple macro for the same purpose. It uses a named cell range as GUI.
Just enter the formulas, values, operators and styles into a cell range, name the cell range, select the range to be formatted and run the macro.
viewtopic.php?f=21&t=6039