Page 1 of 1

Conditional format Calc, LibO 4

Posted: Tue Jul 09, 2013 2:06 pm
by Editor_HU
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

Re: Conditional format Calc, LibO 4

Posted: Tue Jul 09, 2013 2:18 pm
by Villeroy
Relative and absolute references are the key.
Relative A2:B17 is relative. One cell below it refers to A3:B18.

Re: Conditional format Calc, LibO 4

Posted: Tue Jul 09, 2013 2:54 pm
by Editor_HU
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.

Re: Conditional format Calc, LibO 4

Posted: Tue Jul 09, 2013 3:12 pm
by Villeroy
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).

Re: Conditional format Calc, LibO 4

Posted: Tue Jul 09, 2013 3:15 pm
by Editor_HU
redgreen1.ods
Original conditional formatting
(7.28 KiB) Downloaded 235 times
redgreen2.ods
One cell inserted in the middle of the range
(7.59 KiB) Downloaded 195 times
redgreen3.ods
More cells after paste - still only two lines but many ranges
(8.32 KiB) Downloaded 227 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.

Re: Conditional format Calc, LibO 4

Posted: Tue Jul 09, 2013 3:17 pm
by Editor_HU
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 182 times

Re: Conditional format Calc, LibO 4

Posted: Tue Jul 09, 2013 3:40 pm
by Villeroy
Select A1:B17
Format>Conditional...
Formula Is: STYLE($A$1)

Re: Conditional format Calc, LibO 4

Posted: Tue Jul 09, 2013 4:37 pm
by Editor_HU
Thank You Villeroy, unfortunately I could not explain my problem to you. You are not responding to the problem.
Please see the marked field
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.

Re: Conditional format Calc, LibO 4

Posted: Tue Jul 09, 2013 8:15 pm
by acknak
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.

Re: Conditional format Calc, LibO 4

Posted: Wed Jul 10, 2013 11:19 am
by Editor_HU
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.

Re: Conditional format Calc, LibO 4

Posted: Wed Jul 10, 2013 1:50 pm
by acknak
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.

Re: Conditional format Calc, LibO 4

Posted: Wed Jul 10, 2013 3:14 pm
by Editor_HU
Tried to paste as object, it behaves like pictture not as table. I'm talking about multi-page tables with running headers.