[Solved] Copy style to new sheet

[pinkwaz]

I can't seem to find how to copy styles I've created in one document to another. Also, is there a way to apply more than 3 conditional formats to a range?

[jrkrideau]

I can't seem to find how to copy styles I've created in one document to another.

Create a new template from the spreadsheet and use it. File > Template > Create etc.

Have a look at the Style and Template chapters at
OpenOffice.org 3 User Guides

[ken johnson]

If you copy cells with the styles in the first doc then paste those cells into the second doc you will find that the styles will now be in the second doc.
For more than 3 cond formats you use "Formula is" then the STYLE function with an argument that is a formula that returns the appropriate style name.
Attached doc uses STYLE(VLOOKUP($Sheet1.C1;$Sheet1.$A$1:$A$7;2)) with lookup table in A1:B7 to control which of 7 styles (Default, Green, Red, Blue, Magenta, Yellow, Cyan) is applied to C1.
C1<1, Default
C1<2, Green
C1<3, Red
C1<4, Blue
C1<5, Magenta
C1<6, Yellow
C1>=6, Cyan

More than 3 cond formats.ods

(8.7 KiB) Downloaded 380 times

Ken Johnson

[pinkwaz]

I downloaded your doc and I think I'm close to getting this, but can't seem to figure out where the formulas are for c1<#. Can I have data on sheet one with a conditional formatting that tests a cell and pulls the formatting data from sheet 2?

[ken johnson]

The formula for C1<# is the VLOOKUP function that is inside the STYLE function in the Conditional Formatting dialog...
VLOOKUP($Sheet1.C1;$Sheet1.$A$1:$A$7;2)
Important thing to notice is the absence of the 4th argument in this VLOOKUP formula, ie the formula is not: VLOOKUP($Sheet1.C1;$Sheet1.$A$1:$A$7;2;0)
Without that zero, VLOOKUP does not look for a perfect match between the C1 value and the values in Sheet1.A1:A7.
Instead it looks for the greatest value that is less than or equal to C1's value.
It is essential that the values in Sheet1.A1:A7 be in ascending order for VLOOKUP to return a meaningful result when used this way.
Now with Sheet1.A1:A7 = {0;1;2;3;4;5;6} and C1=5.5, the VLOOKUP matches 5.5 with 5 because there is no 5.5 but 5 is the largest column A value that does not exceed 5.5.
With Sheet1.B1:B7 = {"Default";"Green";"Red";"Blue";"Magenta";"Yellow";"Cyan"}, the VLOOKUP goes on to return the value corresponding to the 5, ie "Yellow".
So, the style applied to C1 is then "Yellow".

Can I have data on sheet one with a conditional formatting that tests a cell and pulls the formatting data from sheet 2?

The lookup table can be on any sheet.
It can even be a part of the VLOOKUP formula...
VLOOKUP($Sheet1.C1;{0;"Default"|1;"Green"|2;"Red"|3;"Blue"|4;"Magenta"|5;"Yellow"|6;"Cyan"};2)

What conditional rule are you trying to apply to your cell(s)?

Ken Johnson

[pinkwaz]

OK, Ken, I'm going to play with this some more this morning. I have been playing with a project for a few weeks and it the purpose should have been laid out and planned at the beginning. Instead, I'm playing with Calc and trying to write macros and formulas like I used to in Lotus 1-2-3 to manipulate statistical data. I'm trying to find tutorials to do this with a data base eventually. The spread sheets are for analyzing my golf scores. The conditional formatting is to show a different background for each condition (birdie, par, bogey, double bogey, triple bogey, other). In a cell in my table I measure my score against par to achieve a number. I think I will need a separate formula for each cell if I use your formula. Correct?

[pinkwaz]

round4.ods

a data file

(17.84 KiB) Downloaded 334 times

[acknak]

Here's one with the conditional formatting.

Just a couple of notes:
1) The text in the style table has to match the style names exactly--no extra spaces; letter case is significant
2) You need separate keys for the total scores if you want them styled separately; they can share the style names, as in the sample

3) Recent versions of Calc do not work well with this kind of conditional formatting: it causes Calc to use a lot of extra processing power, enough to slow down the application response with menus and other actions. You can see it getting sluggish even with these few cells.

4) This kind of formatting also causes a problem with notes in the sheet: they won't pop up when the mouse is over a cell with a note.

There are bug reports for these, but who knows when/if it will get any attention. Can't be a very high priority.

[ken johnson]

Similar to Acknak's.
Lookup tables on Sheet2 just in case the table on Sheet1 is only meant to be a visual legend.

round4_KenJ.ods

(18.85 KiB) Downloaded 381 times

Ken Johnson

[pinkwaz]

It took a while for me to conform, but I finally used a combination of the formulas from you both. Thanks very much!

[mgrivas]

It seems that the re-evaluation-that-sucks-cpu problem does not exist anymore or not in Linux anyway.
LibreOffice 3.4.x on (several) Linux.