[Solved] More than 3 conditional formats

Discuss the spreadsheet application

[Solved] More than 3 conditional formats

Postby dante99 » Wed Nov 21, 2012 11:00 am

Hy alll.

i am new user on open office end i need some help with the following,
i need to make a multiple conditional formatting:

value below 80 red background
value 80 to 85 other background color
value 85 to 90 other background color
value 90 to 95 other background color
value 95 to 105 other background color
value 105 to 110 other background color
value higher than 110 other background color

can some one tell me if is possible

thanks
Last edited by dante99 on Wed Nov 21, 2012 2:38 pm, edited 1 time in total.
open office 3.4.959 windows 7 32 bit
dante99
 
Posts: 1
Joined: Wed Nov 21, 2012 10:34 am

Re: [Calc] More than 3 conditional formats

Postby Villeroy » Wed Nov 21, 2012 12:15 pm

dante99 wrote:can some one tell me if is possible

thanks


Yes, it is.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 28652
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: More than 3 conditional formats

Postby Hagar Delest » Wed Nov 21, 2012 12:46 pm

To elaborate on previous post, see this one for example: [Solved] Changing color of currency based on their values.

Please read the [Survival Guide for the forum. This was the underlying message of previous post.

NB: split from the initial topic since this problem can be easily fixed without a macro.

Please add '[Solved]' at the beginning of your first post title (edit button) if your issue has been fixed.
AOO 4.1.7 on Xubuntu 20.04 and 4.1.5 on Windows 10 (with winPenPack port).
User avatar
Hagar Delest
Moderator
 
Posts: 28998
Joined: Sun Oct 07, 2007 9:07 pm
Location: France

Re: [Solved] More than 3 conditional formats

Postby bikehounds » Fri Jan 10, 2020 11:24 pm

top result on google comes to this completely unhelpful page. I registered an account only to give a big, sarcastic thank you - that's how obnoxious this answer is. a moderator should delete this entire thread and get it off of google.
open office 4.1.7 on windows 10
bikehounds
 
Posts: 5
Joined: Fri Jan 10, 2020 11:19 pm

Re: [Solved] More than 3 conditional formats

Postby Villeroy » Sat Jan 11, 2020 2:34 pm

+1
The mere question IF it is possible was appended in 2012 to a topic of 2008 which described at least 2 methods. Moving this truely stupid question out of its context does not make it more comprehensible.
Today the answer would be: "Install LibreOffice instead of OpenOffice".
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 28652
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Solved] More than 3 conditional formats

Postby Jeff » Mon Jan 13, 2020 12:57 pm

Hello,

Villeroy wrote:Today the answer would be: "Install LibreOffice instead of OpenOffice".

Or install CF+ extension

Regards
AOO 4.1.7 on Windows 10 and Xubuntu x64
User avatar
Jeff
 
Posts: 25
Joined: Sat Apr 25, 2009 12:02 pm
Location: France

Re: [Solved] More than 3 conditional formats

Postby bikehounds » Mon Jan 13, 2020 7:08 pm

Thanks, but "install a different program" is equally unhelpful.

I have figured out how to do this, and will put the solution here for future google users so that the title of this thread as "Solved" is actually accurate.

The first step is to create the styles that you want to be able to apply, and name them. There are several ways to do this, the easiest one in my opinion is to style a sample cell the way you want and then create a style from that cell. Instructions for this can be found here:
https://wiki.openoffice.org/wiki/Docume ... new_styles

Once you have your styles created ( I will use the examples Green, Yellow, Red1, Red2, Red3 below), you can create unlimited conditional formatting as follows:

1. Highlight entire range you'd like to format. Drag from the top left to bottom right so that the active cell in the selection is the last cell of the last row.
2. Take note of the letter of the column which you would like to compare the conditional against (in my example I will use Column A)
3. Take note of the row number of the bottom of your selection (in my example I will use row 7)
4. Pull down Format -> Conditional Formatting
5. Change Condition 1 drop down from Cell value is to Formula is

Enter a formula like:
Code: Select all   Expand viewCollapse view
STYLE(IF($A7="Good";"Green";IF($A7="OK";"Yellow";IF($A7="Bad";"Red1";IF($A7="Worse";"Red2";IF($A7="Worst";"Red3"))))))


Note that each nested IF statement compares cell $A7 - make sure you are using the Column from step 2 with a dollar sign and the row from step 3 without a dollar sign, and make sure you have the correct number of closing brackets.

2020-01-13 12_02_04-Window.png
open office 4.1.7 on windows 10
bikehounds
 
Posts: 5
Joined: Fri Jan 10, 2020 11:19 pm

Re: [Solved] More than 3 conditional formats

Postby RoryOF » Mon Jan 13, 2020 7:16 pm

An OpenOffice extension, such as Jeff suggested, is exactly that - an extension to OpenOffice that integrates with it and extends its capabilities; hardly another program. Al you have done is reinvented the wheel.
Apache OpenOffice 4.1.7 on Xubuntu 20.04.1 (mostly 64 bit version) and very infrequently on Win2K/XP
User avatar
RoryOF
Moderator
 
Posts: 31541
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: [Solved] More than 3 conditional formats

Postby bikehounds » Mon Jan 13, 2020 7:24 pm

I was specifically referring to the previous post. I do appreciate the link posted by Jeff, however I had already solved it in another way. I'm sorry my posted solution is offensive.
open office 4.1.7 on windows 10
bikehounds
 
Posts: 5
Joined: Fri Jan 10, 2020 11:19 pm

Re: [Solved] More than 3 conditional formats

Postby RoryOF » Mon Jan 13, 2020 7:30 pm

Your post was not offensive, and I'm sorry you have read my post as suggesting that. I hadn't realised that your reference was to Villeroy's post, rather than to Jeff's. LibreOffice stems from OpenOffice code and has better support for MS Office formats (albeit, not perfect support); it is under more active development, which can make newer versions of it less stable than the conservative OpenOffice, which is also being developed, but at a slower rate.

Many Forum volunteers, in their attempt to give best advice, will recommend LibreOffice when appropriate; multiple conditional formatting is provided in LibreOffce Calc.
Apache OpenOffice 4.1.7 on Xubuntu 20.04.1 (mostly 64 bit version) and very infrequently on Win2K/XP
User avatar
RoryOF
Moderator
 
Posts: 31541
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: [Solved] More than 3 conditional formats

Postby bikehounds » Mon Jan 13, 2020 7:34 pm

Thank you, this info is much more helpful.

Meantime, I don't think it is a bad thing to have a solution for Open Office users posted in the thread where the question was asked.
open office 4.1.7 on windows 10
bikehounds
 
Posts: 5
Joined: Fri Jan 10, 2020 11:19 pm

Re: [Solved] More than 3 conditional formats

Postby Villeroy » Mon Jan 13, 2020 8:49 pm

bikehounds wrote:Thanks, but "install a different program" is equally unhelpful.

LibreOffice is not that different. It is the successor to OpenOffice. OpenOffice is almost dead and its "son" or "daughter" has taken over several years ago.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 28652
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Solved] More than 3 conditional formats

Postby bikehounds » Mon Jan 13, 2020 8:54 pm

I get that, but when someone has a spreadsheet open in a program already installed on their computer and they search for a solution to an acute issue, it will hopefully be helpful for them to have something that gets them rolling in that moment instead of advice to uninstall the program they are actively working in and restart with a new installation. I mean, why have an openoffice forum at all if not to help people with openoffice issues? This whole thread is bizarre.
open office 4.1.7 on windows 10
bikehounds
 
Posts: 5
Joined: Fri Jan 10, 2020 11:19 pm


Return to Calc

Who is online

Users browsing this forum: No registered users and 30 guests