[libreoffice-users] Re: Multuple rules/conditions to range of cells

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.


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.
0       Red_Bold
1000    Red
2000    Blue_Bold
3000    Blue
4000    Orange
5000    Default
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