On 6 Jun 2011 at 10:55, Andreas Säger wrote: To: [email protected] From: Andreas Säger <[email protected]> Subject: [libreoffice-users] Re: Multuple rules/conditions to range of cells Date sent: Mon, 06 Jun 2011 10:55:43 +0200 Send reply to: [email protected]
> 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 [email protected] > In case of problems unsubscribing, write to [email protected] > 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:[email protected] mailto:[email protected] 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 [email protected] In case of problems unsubscribing, write to [email protected] 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
