Just one sheet refuses to adher to conditional formatting

English
,
#1

I have 5 sheets with the same conditional formatting (honestly, I don’t understand if they’re really shared or sheet specific, the manage pop-up doesn’t really say).

However, on sheet 6 (which is basically a clone of the others), the conditional formatting does not work.

I see no difference between the sheets. When I try to manage the conditional formatting with that sheet selected, it looks exactly the same as other sheets.

If I clear direct formatting, the conditional formatting does not appear.

The only thing I can see is that on the problematic sheet 6 I can’t right-click on a cell to manage the direct formatting like on the other sheets. What else can I look for?

image
image1422×1352 74.7 KB

#2

Your conditional formatting is always looking at sheet Räkning1, if the data there is different to that on sheet 6 then you should not expect to get sensible results from the conditional formatting as part of it will be looking at a different sheet.
Do you need to specify the sheet? If not delete the sheet reference.
See the problem here where the second sheet is looking at the data from the first sheet, not the second sheet?
ConditionalRefSheet1CopiedToDifferentSheet.ods (17.7 KB)

#3

Thanks, but that’s not the issue I’m having. The issue is with the range “T3:T18;A3:A17” which works on all sheets except the problematic one.

#4

Maybe you need to define the range for Sheet6, so in the range enter Sheet6.T3:T18;A3:A18 and OK. It seems to nudge the system into looking at the current sheet although the sheet name is not saved.
See also Erratic Conditional Formatting problem and tdf#126327

#5

Could it be a protected sheet?

#6

No, those protection options look just like all other sheets.

Something I cannot understand either which might help me solve the mystery of the sheet with completely refuses conditional formatting, is that I found that one sheet looks like this when you right-click and choose "manage condition formatting on the left column of cells:

image
image2342×1582 199 KB

However, on the very same sheet, when you right-click the rightmost column of cells it looks like this (and it doesn’t say “manage”, it just says “conditional formatting”):

image
image2342×1582 107 KB

Why do I get two different behaviors and different looks for these popups within the very same sheet?

#7

I did manage to to get a condition similar to what you describe. As I said in my earlier comment, I forced the conditional format to look at the current sheet by entering the sheet name in the Range. After OK the sheet name disappeared but the conditional format then looked at the current sheet, not the sheet it was copied from.
I don’t know the sheet name you use for your problematic sheet but if it is Räkning6 then in the Range field enter
CellRangeRakning

Probably because column A is referenced in all those conditional formats while column S is only represented in one conditional format. Better to click Format > Conditional > Manage and select relevant condition there

#8

If do you secondary click on a cell, you will get Manage Conditional Formatting when there are two or more conditions applied to that cell, and Conditional Formatting when there is only one condition applied.

#9

Can you share a copy of the file to test? Delete all sensible data before. Thanks.

#10

conditional-issue.ods (12.7 KB)

I’m now convinved that Giem2 and Giem3 for some reason share their conditional formatting formulas, while on the other sheets (which I removed), the conditional formats are local to the sheets.

This is (to me) still not at all apparent in the UI and I do not know how to change it!

#11

You can’t manage (at least for what I tested) conditional formatting of another sheet, just of the active.

You can select both sheets, and remove a condition, and it will erase only for the visible sheet. So, the conditions in Giem2 and Giem3 are not shared, but equal.

Don’t relay in the context menu to access the conditional formatting.
But the way, your second screenshot (the first in your second comment) shows fragmented ranges, as if you copy and pasted formatted cells/rows.

#12

Giem2 and Giem3 are not identical at all; the ranges are different sizes, the order in the conditional Formats dialogue is different. The order is important for how conditional formatting works when you have overlapping ranges as you have.

conditional-issue_OrderOf Operation
conditional-issue_OrderOf Operation718×464 16.7 KB

2 Likes
#13

Thanks everyone for the tips, I got it working now (though I’m uncertain what I actually did, unfortunately).

What’s still super confusing is that when I press new sheet, and then choose manage conditional formatting… I have three formulas there already! Why? I didn’t duplicate a sheet.

AND I now notice that when I Remove some of those formulas from my new sheet and press OK, they come back!! I’m not allowed to remove them (AND they don’t work, btw… so once again, I feel like I’m seeing the formulas of a different sheet than the selected one when I go to manage).

EDIT: THE CONDITIONAL FORMATTING IS NOW GONE FROM A DIFFERENT SHEET THAN THE ONE I WAS EDITING! Yet, when I go to manage the formulas in that sheet, they appear as if they are there!!!

I’m 100% convinced this is now bugged. When I click manage in one sheet, I AM SEEING THE FORMULAS OF A DIFFERENT SHEET but I do not know which one.

EDIT: I just restarted Calc without saving and now on the new sheet, there are no conditional formulas to manage. 100% bugged, but I don’t know what triggers is. I’m on 24.8.4.2 in Linux.

EDIT: Found the repeat: 1. Go into sheet with no conditional formulas, manage. Observe it being empty. 2. Go into sheet with conditional formulas, manage. Observe the list. 3. Go back into sheet with no conditional formulas, manage. Observe a list that looks indentical to the previous sheet.

EDIT: Ok, so the workaround is, any time I want to edit a conditional formula via manage, I go to the sheet I want to edit. Save. Quit. Restart Calc, and then I can manage the current sheet’s conditional formulas.

No wonder this was so confusing before! :laughing:

EDIT: Holy shit, even more conditional craziness! If you drag copy cells across a range with conditional formatting, the conditional formattings simply get erased! AND when you go into manage, and try to add back those ranged by editing the existing formulas, you get DUPLICATES of them!! How buggy is this feature???

EDIT: The above behavior went away after a restart (only the above, btw… the manage issues I can still recreate). I swear it was there and this is the glitchiest piece of mature software feature I’ve encountered since the linked libraries in Blender which are still super buggy after half a decade! (Still occasionally donating to both though, so hopefully this will maybe get fixed in another decade or so.) :face_with_peeking_eye:

#14

Having the same issue. Do you know if a bug report has been filed for this?

JF

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