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+h...@libreoffice.org
In case of problems unsubscribing, write to postmas...@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
