Desperate for a fourth conditional format

Discuss the spreadsheet application

Desperate for a fourth conditional format

Postby Pugilist_Puppy » Sun Jul 19, 2020 6:22 pm

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.
Last edited by MrProgrammer on Mon Jul 20, 2020 5:51 am, edited 1 time in total.
Reason: Changed subject, was: Desperate For A Fourth
OpenOffice 3.4.1 on Windows 7 Home Premium
Pugilist_Puppy
 
Posts: 121
Joined: Thu Jan 14, 2010 5:11 pm

Re: Desperate For A Fourth

Postby RoryOF » Sun Jul 19, 2020 7:02 pm

There is an extension to offer more conditional formats. Download this from
https://extensions.openoffice.org/en/project/extend-your-conditional-formatting
Apache OpenOffice 4.1.9 on Xubuntu 20.04.2 (mostly 64 bit version) and very infrequently on Win2K/XP
User avatar
RoryOF
Moderator
 
Posts: 32348
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: Desperate For A Fourth

Postby Zizi64 » Sun Jul 19, 2020 7:27 pm

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.
Tibor Kovacs, Hungary; LO6.1.6, 6.2.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-6.4.5;AOO4.1.7
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
User avatar
Zizi64
Volunteer
 
Posts: 9787
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Desperate For A Fourth

Postby keme » Mon Jul 20, 2020 1:41 am

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.
User avatar
keme
Volunteer
 
Posts: 3404
Joined: Wed Nov 28, 2007 10:27 am
Location: Egersund, Norway

Re: Desperate For A Fourth

Postby Lupp » Mon Jul 20, 2020 12:31 pm

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.
Attachments
pseudoCFbySTYLEfunction.ods
(9.98 KiB) Downloaded 36 times
On Windows 10: LibreOffice 7.0 and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
User avatar
Lupp
Volunteer
 
Posts: 3034
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: Desperate for a fourth conditional format

Postby Villeroy » Mon Jul 20, 2020 5:24 pm

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. 
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
Villeroy
Volunteer
 
Posts: 29465
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany


Return to Calc

Who is online

Users browsing this forum: waynec444 and 12 guests