How to use the Manage Conditional Formatting dialogue?

English
,
#1

I am having trouble figuring out conditional formatting. For extremely simple cases, such as the value of the current cell, I can get it to work. But when it comes to formatting the current cell according to a formula, I’m getting nowhere. And my formula is referring to values in another sheet which is doubly confusing.

Worse, the process of defining and debugging is having me tearing my hair because I cannot get the “Manage Conditional Formatting“ (MCF) dialogue to work. If I can get that dialogue working, that might help solve my other problems so I’ll start there.

When I open the MCF dialogue, it shows me the conditional formats that I have defined. On Sheet 1 (Input Lines) the Range A10:A506 has a condition. On Sheet 4 (CategoryTotals) the range B10:B506 has a similar condition. I cannot find anything, anywhere, that will allow me to see, change, aura define what sheet either of those ranges is actually meant to be in. As far as I can tell from the dialogue, the condition applies to those two ranges in all sheets, which is, of course, not what I want.

I can edit the condition and insert the sheet name, following the usual syntax, and a syntax checker highlights the range in red until I have the syntax correct. But then when I save the changes, it deletes the sheet name. It’s as if the MCF dialogue dates from a time when the app could only handle a single sheet and has never been updated. That is just a guess, and it seems absurd.

So my first question is, how do I define, edit, or verify the sheet In a conditional formatting range?

Because the formatting is not working for me, to debug I attempt to delete those two formats and start over. In the MCF I select one and click Remove. I select the other and click Remove. Both conditional formats are now gone from the dialogue. I click OK. I reopen the MCF dialogue and both conditional formats are restored, they weren’t deleted even though it looked like they had been deleted. Deleting them one at a time is not a workaround.

Question two: how do I delete conditional format in the MCF dialogue?

I have read all the help pages and, to the best of my understanding, I’m doing what I’m supposed to do. I have searched this forum as well as general web searches, and everything that I read says I’m using it properly. But it’s not working.

#2

ask118968.ods (13.1 KB)

#3

Thanks Andreas, it help a LOT to have a working example to start from. I’ve used your file and made various changes to it, and it keeps working. It has the sheet name in the formula and I can change the name of the sheet and the formula is automatically updated, as expected. I have not yet figured out what the problem in my file is, but at least I have a working sample to compare to. One unnecessary workaround I did was to put the condition formula in a hidden column of the same sheet and then test that value for formatting. That worked, so I made erroneous assumption that conditions had to be in same sheet. But your sheet works the way I want it to, so the problem is somewhere else.

#4

@Villeroy – I’m having a curious problem with your sample sheet, which probably has to do with the RANDOM function. In Sheet2 I see values <.25 in rows 3,6,9,19,21,22,23,25,27 but the rows with no conditional formatting applied are rows 5,10,11.12,17,26,29,30. Obviously this is irrelevant to my Original Problem, but I’m curious what’s going on. It would be all too easy to presume the conditional test function was not being applied properly. I’m wondering if maybe when the sheet loads, the conditional is calculated first and then the new values of RANDOM are calculated. Again, a curiousity, not a problem.

#5

Four styles:

  1. “Warning” applied to A1 (fall back if no condition is met)
  2. “Bad” applied to A1 if $Sheet2.$A1 <0.25
  3. if not 2: “Neutral” applied to A1 if $Sheet2.$A1 <0.5
  4. if not 3: “Good” applied to A1 if $Sheet2.$A1 <0.75
#6

Thanks. I’ll look again. I only saw 2,3,4 as conditionals and missed the fall back.

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