Can conditional formatting also apply multiple styles pertaining to different aspects of a cell (background vs. font)?

English
#1

I have a spreadsheet in which I apply conditional formatting based on dates.

I have a named range called HOLIDAYS which I use to figure out the state of a given date (holiday, weekend …). The following formula is used to figure out if a day is a workday (1) or not (0). The date of the current row is stored in COLUMN()+2, just so this makes sense.

NETWORKDAYS.INTL(INDIRECT(ADDRESS(ROW();COLUMN()+2;1)); INDIRECT(ADDRESS(ROW();COLUMN()+2;1)); 1; HOLIDAYS)

I want to apply two formats that are in fact unrelated.

  1. One to set the background based on whether the week number is even or odd.
  2. The other to set the color and font style of the cells based on whether a day is a workday or not.

Therefore it would be great if I could have a condition that applies a style to the background and another that applies it to the font itself. I haven’t found such a way, therefore this question.

Currently instead I defined custom cell styles:

  1. Workday in even week (font bold etc), based on Week is even (see below)
  2. Workday in odd week (font bold etc), based on Week is odd (see below)
  3. Week is even (background yellow)
  4. Week is odd (no background)

Then, using the following corresponding formula I check to apply the respective style (same order):

  1. AND(IF(INDIRECT(ADDRESS(ROW(); 2; 1))=0);ISEVEN(INDIRECT(ADDRESS(ROW(); 1; 1))))
  2. AND(IF(INDIRECT(ADDRESS(ROW(); 2; 1))=0);ISODD(INDIRECT(ADDRESS(ROW(); 1; 1))))
  3. ISEVEN(INDIRECT(ADDRESS(ROW(); 1; 1)))
  4. ISODD(INDIRECT(ADDRESS(ROW(); 1; 1)))

Here the column 2 contains the result from the formula at the top, stating if a day is a workday (1) or not (0). Column 1 contains the week number, which governs the background style.

This works, but it seems cumbersome.

Is there a more succinct way to apply two styles - where the styles control unrelated aspects such as background vs. font style - using conditional formatting?

Thanks.

1 Like
#2

No, not only using conditional formatting, but also using any method. In Calc, cell styles define 100% of formatting options, and thus, applying any style overrides 100% of the options, thus making several styles applied at once completely useless.

This is related to tdf#149271.

(Note that your last condition is redundant, because at that stage, you already know that everything else was ruled out by the previous conditions, thus you may simply put 1 (“always true”) as the last condition, or even remove that condition completely, and format the whole range using that fourth style directly, and the three conditions will override it when necessary).

1 Like
#3

Welcome! Yes, there is such a way and it is not very difficult. Since there are only four options, you only need four styles and three conditions. But before talking about conditional formatting, please tell us why you use the INDIRECT(ADDRESS(ROW();COLUMN()+2;1)) construct? Is the usual addressing - absolute and relative - not enough for your tasks?

1 Like
#4

Please enlighten me what is the “usual addressing”?! I want to avoid absolute addressing, though. And I think I even have to, since this formula covers a whole range of cells with multiple rows and columns.

Maybe you know a way to refer to the cells of the current row that is better than my approach.

I found it cumbersome to write initially, but since it did the job I saw no reason to change it.

#5

Well, since I can base a style on another it’s not too troublesome, but I thought perhaps there is a less obvious but more succinct way. Thanks. Curiously the other answer seems to contradict your claim, which on the other hand is backed by the description in tdf#149271.

I know and I understand. And if this were somehow a performance issue, I’d probably address it. It doesn’t seem to be (due to the limited number of rows for the days in a year?).

But quite frankly here I’d be confused if one of the conditions were missing. The style under 4 is anyway a “null” style, since it doesn’t diverge from the standard “no background”. But I prefer to formalize it, in case I ever change my mind about what a work day in an odd week should be formatted like.

But thanks for pointing it out! It’s quite different to work this way than with all the conventional programming languages I know and I appreciate any insights from those more accustomed to the idiomatic ways of doing stuff in LibreOffice.

#6

I suppose it doesn’t, and I bet it’s just a matter of terminology :slight_smile: - I answered literally, while Vladyslav will provide a way to solve the task of formatting with simpler way (but that would not mean that two cell formats will be active at the same time, defining different subsets of the resulting cell formatting - unless it’s by inheritance) - after all, the Ask site is about helping users make their job done, in which Vladyslav is one of the best wizards here.

2 Likes
#7

Yes, it is - Mike answered “No” to the question “Is it possible to combine two styles in one cell?”, and I answered “Yes” to the question “Is it possible to create such conditional formatting?”

LotsOfStylesInConditionalFormatting.ods (30.1 KB)

2 Likes
#8

Conditional formatting is an important use case for multiple-concurrent styles support (which is what I ask for in my bug).

My thinking is that if (X)HTML can have elements with multiple CSS styles applied - why shouldn’t LibreOffice, whose document representation format is ODF, which is XML-based, not have the same thing?

1 Like
#9

Note that CSS has no categories of styles. ODF styles, although inspired by CSS, and refer to CSS in many aspects, are still not equal to CSS. The idea of categories (in Writer case, it’s paragraph/character/page/…) makes it both more complex and more simple. And being a WYSIWYG editor, the concept of order of application is less convenient, other than based on categories. I don’t see how “if CSS has it, why can’t LO” could be a useful argument; rather, a clear specification of behavior and manipulations is needed, to evaluate the up/downsides. Even though I clearly understand why it could be useful (especially here), I still don’t see how it could be implemented usably.

#10

In Excel, when creating a cell style, we explicitly indicate in the corresponding Checkboxes what the style includes: Number, Alignment, Font, Border, Fill, Protection.

#11

I can’t tell anything about Excel, but…
As I see it for Calc:
For style families fully supporting heritage (exactly 1 in Calc), there are attribute families. For any of these families you can use the Standard button to tell: UseInherited.
Strangely for Write (3 such style families) the functionality (supposedly the same) is labeled more clearly Reset to Parent.
Is there a difference I didn’t yet understand?

#12

Which was named so in tdf#128469; and it’s simply an oversight that this was limited to Writer. A respective bug report is needed, with a See Also to the previous bug.

I see; but how does it work with regards to applying several styles at once? Or is it only useful for combining static style and conditional formatting? (But the latter doesn’t use Excel styles, IIUC…?)

#13

In Excel, for example, we can have one style that responds to number formats (A), the other - for cell borders (B) and use only one style for a particular cell, or both styles in sequence (applying style A to a cell will only change the number format, and applying style B will only change the cell borders).
For conditional formatting, the situation is similar (although cell styles are not used).
For example, we create one conditional formatting rule that changes the number format of a cell and another rule that changes the cell’s borders. These two rules can be applied to a particular cell individually or simultaneously.


In my opinion, using styles for conditional formatting (instead of direct formatting) looks more logical and would benefit Excel. :slightly_smiling_face:

#14

OMG. I have tried it … lol.
So in Excel, a cell can only have one style applied. All previous styles get unapplied as soon as you apply the next style; and their properties get embedded into the cell properties as direct formatting.

To test, in Excel I created a “Border1” style (thick solid border, red), and “Fill1” style (yellow fill). Obviously, these styles only had respective property groups checked.

I applied both styles to a cell (first, “Border1”, then “Fill1”), and as expected, the cell got both thick red border, and yellow fill.

Then I edited both styles, making “Border1” border color blue, and “Fill1” fill green. And only the “Fill1” change got applied to the cell, but “Border1” change didn’t apply to the cell.

The net result is: there is no usable style system in Excel, which would serve as a model for Calc. And the question about a usable logical system is still unresolved.

ExcelMultiStyles.gif

1 Like
#15

In fact my current solution – outlined in the question – uses the technique of inheriting one style from another. However, the issue is still that it makes the considerations of how to apply them more complex.

After all if I could tie a single condition two a single (composite) style it would become a little more succinct, especially once we add more conditions and (composite) styles.

That said, I can cope with the complexity, there are aspects in other programming languages that require much more mental juggling :wink:

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.