Conditional format Calc, LibO 4

[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

[Villeroy]

Relative and absolute references are the key.
Relative A2:B17 is relative. One cell below it refers to A3:B18.

[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.

[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).

[Editor_HU]

redgreen1.ods

Original conditional formatting

(7.28 KiB) Downloaded 237 times

redgreen2.ods

One cell inserted in the middle of the range

(7.59 KiB) Downloaded 197 times

redgreen3.ods

More cells after paste - still only two lines but many ranges

(8.32 KiB) Downloaded 228 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.

[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 183 times

[Villeroy]

Select A1:B17
Format>Conditional...
Formula Is: STYLE($A$1)

[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

'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.

[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.

[Editor_HU]

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.

[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.

[Editor_HU]

Tried to paste as object, it behaves like pictture not as table. I'm talking about multi-page tables with running headers.