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


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

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.