Conditional format Calc, LibO 4

Discuss the spreadsheet application

Conditional format Calc, LibO 4

Postby Editor_HU » Tue Jul 09, 2013 2:06 pm

Hello, by chance does anybody know a way to define range of conditional formatting by specifying a range name?

When I define rules and set ranges like A2:B17, any paste into a cell within the range will ruin the definition, I have hard time managing the rules and ranges. Using named ranges would help a lot.

Thank you in advance
LibreOffice 6.3.4.2 on Windows 8, Dell Inspiron Core i7
Editor_HU
 
Posts: 153
Joined: Tue Sep 20, 2011 1:04 pm

Re: Conditional format Calc, LibO 4

Postby Villeroy » Tue Jul 09, 2013 2:18 pm

Relative and absolute references are the key.
Relative A2:B17 is relative. One cell below it refers to A3:B18.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 28553
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Conditional format Calc, LibO 4

Postby Editor_HU » Tue Jul 09, 2013 2:54 pm

The problem is not about the condition formula, it's about the range where the condition should be considered and the conditional format be applied.

If I only have one definition, effective for A2_B17 range, and I paste a content (accidently with format) to say B8, I will have four lines in the list of the conditional formatting list. It will break up the so far continuous range, cut out the cell where I inserted content, the condition wil not be effective there, and the rest gets covered by several smaller tiles. And I can't manage the rule any more.

Whenever I try to enter a range name as area of validity, the input cell gets red, range names not accepted. When I try to enter $A$2:$B$17, I can enter, but the absolute indicators are gone when I open the rule again.
LibreOffice 6.3.4.2 on Windows 8, Dell Inspiron Core i7
Editor_HU
 
Posts: 153
Joined: Tue Sep 20, 2011 1:04 pm

Re: Conditional format Calc, LibO 4

Postby Villeroy » Tue Jul 09, 2013 3:12 pm

Conditional formatting is a good example for what goes wrong in LibreOffice development. Please attach a file with a clear description what you want to have conditionally formatted, how (styles) and when (conditions).
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 28553
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Conditional format Calc, LibO 4

Postby Editor_HU » Tue Jul 09, 2013 3:15 pm

redgreen1.ods
Original conditional formatting
(7.28 KiB) Downloaded 132 times


redgreen2.ods
One cell inserted in the middle of the range
(7.59 KiB) Downloaded 89 times


redgreen3.ods
More cells after paste - still only two lines but many ranges
(8.32 KiB) Downloaded 109 times


See the examples one after the other. You can type "red" or "green" into cell A1, the result gets visible. In these 3 examples the range of validity splits up, a hole is cut in the continuity of the validity area. But we still only have 2 rows of conditional formats.
LibreOffice 6.3.4.2 on Windows 8, Dell Inspiron Core i7
Editor_HU
 
Posts: 153
Joined: Tue Sep 20, 2011 1:04 pm

Re: Conditional format Calc, LibO 4

Postby Editor_HU » Tue Jul 09, 2013 3:17 pm

redgreen4.ods
A format brush was applied. Select format, conditional format, manage - uncountable number of rows appeared, the rules of formatting are not manageable any more.
(8.89 KiB) Downloaded 85 times
LibreOffice 6.3.4.2 on Windows 8, Dell Inspiron Core i7
Editor_HU
 
Posts: 153
Joined: Tue Sep 20, 2011 1:04 pm

Re: Conditional format Calc, LibO 4

Postby Villeroy » Tue Jul 09, 2013 3:40 pm

Select A1:B17
Format>Conditional...
Formula Is: STYLE($A$1)
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 28553
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Conditional format Calc, LibO 4

Postby Editor_HU » Tue Jul 09, 2013 4:37 pm

Thank You Villeroy, unfortunately I could not explain my problem to you. You are not responding to the problem.

redgreen.JPG
Please see the marked field


'Circled' the field where I would like to enter a range name, which should be untouched regardless what I paste into the covered set of cells.
I wish it remained A2:B17 as I typed in in the first sample file. Better if I give a name 'Appletree' to A2:B17, and here I only enter 'Appletree'. If I change the definition of range 'Appletree', the scope of conditional formatting changes accordingly.

Instead, a paste somewhere into the middle, splits up the range definition as it shows on the picture, and some cell operations even create many-many formatting rule entries in place of my two original conditional format entries, one for the green, one for the red. Actually, the 4th sample created formatting rules for each and every cell individually, twice for each. Unmanageable.

I still hope there is a more clever way to format, I don't believe people can cope with this.
LibreOffice 6.3.4.2 on Windows 8, Dell Inspiron Core i7
Editor_HU
 
Posts: 153
Joined: Tue Sep 20, 2011 1:04 pm

Re: Conditional format Calc, LibO 4

Postby acknak » Tue Jul 09, 2013 8:15 pm

The problem here--as I understand it--is not going to be solved by using a range name, or by anything to do with the conditional format configuration. The problem is caused by the paste operation itself: Calc replaces all formatting, including the conditional formatting, with the formatting from the pasted content.

The only way I know of to avoid that is to use Edit > Paste Special and specify that you don't want to paste the formatting, or to use an intermediate copy/paste step and paste into Calc as unformatted text.

I don't think there is any way to paste some formatting but not the conditional formatting.
AOO4/LO5 • Linux • Fedora 23
User avatar
acknak
Moderator
 
Posts: 22756
Joined: Mon Oct 08, 2007 1:25 am
Location: USA:NJ:E3

Re: Conditional format Calc, LibO 4

Postby Editor_HU » Wed Jul 10, 2013 11:19 am

acknak wrote:The problem here--as I understand it--is not going to be solved by using a range name, or by anything to do with the conditional format configuration. The problem is caused by the paste operation itself: Calc replaces all formatting, including the conditional formatting, with the formatting from the pasted content.

The only way I know of to avoid that is to use Edit > Paste Special and specify that you don't want to paste the formatting, or to use an intermediate copy/paste step and paste into Calc as unformatted text.

I don't think there is any way to paste some formatting but not the conditional formatting.


Thank you, that means conditional formatting can not be used for what I used to in other office suites. From one hand it's a pity, from other hand even if it worked, it would have been useless for me. I wanted to ready-make and format a detailed, subdetailed list, categories highlighted, summary at the end, so the nice looking worksheet (range in a worksheet) could be copied and inserted into Writer as formatted but disconnected table. I figured out that conditional formatting gets lost during copy-paste, so the visual effect I wanted to achieve will not appear, the table gets flattened.
LibreOffice 6.3.4.2 on Windows 8, Dell Inspiron Core i7
Editor_HU
 
Posts: 153
Joined: Tue Sep 20, 2011 1:04 pm

Re: Conditional format Calc, LibO 4

Postby acknak » Wed Jul 10, 2013 1:50 pm

Wait, you want to copy/paste a formatted range from Calc into Writer?

The default action in that case, at least for me, is that the spreadsheet range is pasted as an embedded object, which should retain all the formatting.

I guess I still don't understand what you want.
AOO4/LO5 • Linux • Fedora 23
User avatar
acknak
Moderator
 
Posts: 22756
Joined: Mon Oct 08, 2007 1:25 am
Location: USA:NJ:E3

Re: Conditional format Calc, LibO 4

Postby Editor_HU » Wed Jul 10, 2013 3:14 pm

Tried to paste as object, it behaves like pictture not as table. I'm talking about multi-page tables with running headers.
LibreOffice 6.3.4.2 on Windows 8, Dell Inspiron Core i7
Editor_HU
 
Posts: 153
Joined: Tue Sep 20, 2011 1:04 pm


Return to Calc

Who is online

Users browsing this forum: Google [Bot] and 9 guests