Apache OpenOffice Community Forum

After years of using this most wonderful programme, and stunningly it's free, i have been blighted by one drawback. I have implemented Calc spreadsheets in scores of helpful ways and it's been great fun and at times fascinating.

On to the downside, again and again and again, i have so desperately needed an option of a fourth conditional format to be available. I'm not greedy and wanting after five, four will be superb. Over all the years a fourth would have been like reaching the promised land.

Now i know the drop down menu only provides for three. Is there any way i can add a fourth parameter by editing the code in Calc's code window? I have used SQL and PHP etc for years, also dabbled with other coding languages, so i would be able to construct a query. I just need to know where i can place it to append to the standard three formatting conditions currently provided. I'll hunt down the language and syntax myself.

I realise Calc is not a database but can i use the drop down menu within the three parameter box and enter a MySql 'type' code, or javascript somewhere to provide four options, either in the first formatting box field or in the 'code' window view?

Warmest thanks in advance of replies, even if to say it not possible.

There is an extension to offer more conditional formats. Download this from
https://extensions.openoffice.org/en/pr ... formatting

On to the downside, again and again and again, i have so desperately needed an option of a fourth conditional format to be available. I'm not greedy and wanting after five, four will be superb. Over all the years a fourth would have been like reaching the promised land.

The LibreOffice Calc supports more than three conditions. Try it.

If you don't want to install extensions or change to LibreOffice, here is a workaround: Use the STYLE() function in a conditional format formula. If the function is evaluated, it overrides the style assigned by the conditional formatting mechanism.

keme wrote:... If the function is evaluated, it overrides the style assigned by the conditional formatting mechanism.

I think this is not quite correct.

In fact the style chosen with the help of the formula evaluating STYLE() is actually assigned to the afflicted cells. STYLE() itself always returns 0 (zero). If this is the result of the formula , it's treated as FALSE concerning the effect for CF. If the formula defining the condition can in addition return a value different from 0, the respective CF will be overlaid to the assigned cell style, whether it was set in advance or assigned due to the evaluation of STYLE().

As I judge the situation as described to be rather convoluted concerning the immediate effects, and probable side-effects only showing later as well, I tend to dissuade from using this workaround.

See the demonstration in the attached example.

I still like my little Python macro for unlimited c.f. even when using LibreOffice. The idea is that you write down your conditions to a named cell range as you would in the OpenOffice dialog. I find this even easier than any Open/LibreOffice dialog. All you need to understand is how relative references work. Then you select the target range(s), take care of the active input cell and call the macro to transfer your cell values into the selected range's c.f.

[Calc] More than 3 conditional formats

Edit: I modified the code to work with recent versions of LibreOffice as well and wrapped the code into an installer document.