Date: prev next · Thread: first prev next last
2011 Archives by date, by thread · List index


On 6 Jun 2011 at 10:55, Andreas Säger wrote:

To: users@libreoffice.org
From: Andreas Säger <villeroy@t-online.de>
Subject: [libreoffice-users] Re: Multuple rules/conditions to 
range of cells
Date sent: Mon, 06 Jun 2011 10:55:43 +0200
Send reply to: users@libreoffice.org

Am 05.06.2011 18:47, PLO wrote:
Hello LO Users,

I've been trying - without success - to apply five separate 
conditional
formatting rule to a column based on their value. Each of the 
365 cells in
the column contain a simple formula. E.g

=SUM((N151+L152)-(P152+R152))

I need the result in each of the cells to be formatted in 
different colour
if it is over or below a certain value. I need five rules in total. 
So
formatting would be:

< 1000 Red/Bold
=> 1000< 2001 Red
=> 2001< 3001 Blue/Bold
=> 3001< 4001 Blue
=> 4001< 5001 Orange
=> 5001 Default

I have used conditional formatting with success, except that 
it is limited
to three conditions...which isn't enough.

In MS Office 2007 I can apply any number of rules to a range 
of cells
simply by selecting Conditional Formatting from 'Styles' on 
the Ribbon and
adding a rule for each.

Maybe there is another way in LibreOffice but I can't find it.


I've tried another method that seems to give the results. 
Unfortunately, it appears the current() doesn't give the results of 
what is before the style when used in a formula?

=(A1)+STYLE(CHOOSE(MIN(INT(((A1)-((A1)
1000))/1000)+1,6),"Red_bold","red","blue_bold","blue","orange","default"))

I used a single cell for the formula, but the (A1) could be replaced 
with the formula (SUM((N151+L152)-(P152+R152))) or 
(N151+L152-P152-R152) to give the same results. 

Would have been simpler if all the options where < multiples of 
1000 or all <=. I also added max and min, so a negative or higher 
value would fall into the ranges. 

I created styles with the matching names giving the styles.





This requires some preparing set up work.
1. Create a set of 5 cell styles comprising the attributes you 
want to 
see. (hit F11, right-click>New...)

2. Take some free cell range of 6 rows and 2 columns, say 
$X$1:$Y$5.
0Red_Bold
1000Red
2000Blue_Bold
3000Blue
4000Orange
5000Default
First column has threashold values, second column has your 
style names 
which represent the formatting attributes you want to see up to 
the 
respective threashold.

3. Select the cell range you want to color. Notice the row 
number of the 
currently active input cell.

4. menu:Format>Conditional...
Change <Value Is> to <Formula Is> for condition #1.
Formula: STYLE(VLOOKUP($H1;$X$1:$X$6;2))
(assuming the active cell in row #1, compare values in column H 
and the 
compare list in X1:X6).
For the active cell in row 99 and your compare values in column 
M the 
formula would be:
STYLE(VLOOKUP($M99;$X$1:$X$6;2))

Greetings,
Andreas


-- 
Unsubscribe instructions: E-mail to users+help@libreoffice.org
In case of problems unsubscribing, write to 
postmaster@documentfoundation.org
Posting guidelines + more: 
http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/www/users/
All messages sent to this list will be publicly archived and 
cannot be deleted



+----------------------------------------------------------+
  Michael D. Setzer II -  Computer Science Instructor      
  Guam Community College  Computer Center                  
  mailto:mikes@kuentos.guam.net                            
  mailto:msetzerii@gmail.com
  http://www.guam.net/home/mikes
  Guam - Where America's Day Begins                        
  G4L Disk Imaging Project maintainer 
  http://sourceforge.net/projects/g4l/
+----------------------------------------------------------+

http://setiathome.berkeley.edu (Original)
Number of Seti Units Returned:  19,471
Processing time:  32 years, 290 days, 12 hours, 58 minutes
(Total Hours: 287,489)

BOINC@HOME CREDITS
SETI        10840698.799905   |   EINSTEIN     5978952.530851
ROSETTA      3215702.700203   |   ABC          6158864.767707


-- 
Unsubscribe instructions: E-mail to users+help@libreoffice.org
In case of problems unsubscribing, write to postmaster@documentfoundation.org
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/www/users/
All messages sent to this list will be publicly archived and cannot be deleted

Context


Privacy Policy | Impressum (Legal Info) | Copyright information: Unless otherwise specified, all text and images on this website are licensed under the Creative Commons Attribution-Share Alike 3.0 License. This does not include the source code of LibreOffice, which is licensed under the Mozilla Public License (MPLv2). "LibreOffice" and "The Document Foundation" are registered trademarks of their corresponding registered owners or are in actual use as trademarks in one or more countries. Their respective logos and icons are also subject to international copyright laws. Use thereof is explained in our trademark policy.