[Solved] Copy style to new sheet

Discuss the spreadsheet application

[Solved] Copy style to new sheet

Postby pinkwaz » Sun Apr 25, 2010 4:39 pm

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?
Last edited by pinkwaz on Wed Apr 28, 2010 3:05 pm, edited 2 times in total.
OpenOffice 3.2 on Windows Vista
pinkwaz
 
Posts: 14
Joined: Sat Apr 24, 2010 3:38 pm

Re: copy style to new sheet

Postby jrkrideau » Sun Apr 25, 2010 5:29 pm

pinkwaz wrote: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
OpenOffice 4.1.6; Ubuntu 18.04
jrkrideau
Volunteer
 
Posts: 3715
Joined: Sun Dec 30, 2007 10:00 pm
Location: Kingston Ontario Canada

Re: copy style to new sheet

Postby ken johnson » Sun Apr 25, 2010 6:10 pm

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 257 times

Ken Johnson
AOO 4.1.3 on Ms Windows 10
Help improve this forum by clicking the EDIT button on your first post then adding "[Solved]" to its heading once a solution to your problem has been provided.
ken johnson
Volunteer
 
Posts: 918
Joined: Sun May 31, 2009 1:35 am
Location: Sydney, Australia

Re: Copy style to new sheet

Postby pinkwaz » Mon Apr 26, 2010 12:13 am

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?
OpenOffice 3.2 on Windows Vista
pinkwaz
 
Posts: 14
Joined: Sat Apr 24, 2010 3:38 pm

Re: Copy style to new sheet

Postby ken johnson » Mon Apr 26, 2010 4:26 am

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
AOO 4.1.3 on Ms Windows 10
Help improve this forum by clicking the EDIT button on your first post then adding "[Solved]" to its heading once a solution to your problem has been provided.
ken johnson
Volunteer
 
Posts: 918
Joined: Sun May 31, 2009 1:35 am
Location: Sydney, Australia

Re: Copy style to new sheet

Postby pinkwaz » Mon Apr 26, 2010 12:58 pm

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?
OpenOffice 3.2 on Windows Vista
pinkwaz
 
Posts: 14
Joined: Sat Apr 24, 2010 3:38 pm

Re: Copy style to new sheet

Postby pinkwaz » Mon Apr 26, 2010 1:15 pm

round4.ods
a data file
(17.84 KiB) Downloaded 202 times
OpenOffice 3.2 on Windows Vista
pinkwaz
 
Posts: 14
Joined: Sat Apr 24, 2010 3:38 pm

Re: Copy style to new sheet

Postby acknak » Mon Apr 26, 2010 3:22 pm

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.
Attachments
round4_acknak.ods
(14.37 KiB) Downloaded 229 times
AOO4/LO5 • Linux • Fedora 23
User avatar
acknak
Moderator
 
Posts: 22756
Joined: Mon Oct 08, 2007 1:25 am
Location: USA:NJ:E3

Re: Copy style to new sheet

Postby ken johnson » Mon Apr 26, 2010 4:15 pm

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 258 times

Ken Johnson
AOO 4.1.3 on Ms Windows 10
Help improve this forum by clicking the EDIT button on your first post then adding "[Solved]" to its heading once a solution to your problem has been provided.
ken johnson
Volunteer
 
Posts: 918
Joined: Sun May 31, 2009 1:35 am
Location: Sydney, Australia

Re: Copy style to new sheet (solved)

Postby pinkwaz » Wed Apr 28, 2010 3:06 pm

It took a while for me to conform, but I finally used a combination of the formulas from you both. Thanks very much!
OpenOffice 3.2 on Windows Vista
pinkwaz
 
Posts: 14
Joined: Sat Apr 24, 2010 3:38 pm

Re: [Solved] Copy style to new sheet

Postby mgrivas » Sat Mar 10, 2012 8:17 pm

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.
LibreOffice 3.4.x @ Linux (Mint, Ubuntu, openSuSE)
mgrivas
 
Posts: 4
Joined: Thu Mar 08, 2012 10:07 pm


Return to Calc

Who is online

Users browsing this forum: No registered users and 12 guests