Conditional Formatting Question

[Green Biro]

Is there a known issue with using logical operator functions with
conditional formatting?

The reason I ask is that I have a long conditioning formula (for cell 'F5'),
which works fine (ie it obeys the formatting request).

=OFFSET($CH$1,ROW()-1,MATCH(LEFT(F$1,2)&" Class",$CI$1:$DP$1,0))<>""

However, when I change it to

=OR(TRUE,OFFSET($CH$1,ROW()-1,MATCH(LEFT(F$1,2)&"
Class",$CI$1:$DP$1,0))<>"")

it no longer works (ie it no longer obeys the formatting request).


Of course, my actual aim is not just to 'OR' with the TRUE constant, I've
just reduced my problem down to the minimal level for diagnosis and now
don't know where to go.

Any help much appreciated.

Thanks

GB

Oh yes and one other question: Is there any way to enable cursor movement in
the conditional formatting dialog box without it changing the formula?

 

[Green Biro]

I just added OR(TRUE.....) to test. Of course it should evaluate to True
but that's just the problem - the formatting request is not obeyed.

FWIW, AND(TRUE...) does exactly the same thing

In my frustartion, I have simply copied the conditional format as the actual
cell formula and it evaluates to true. But when I paste it back to the
conditional formatting dialog, it doesn't obey the formatting request.
Remove the 'AND' or 'OR' function from my conditional format formula and all
is well again.

Can't think what the problem is...

GB

 

[T. Valko]

I don't know *why* this doesn't work but I can tell you that I've also run
into this when using conditional formatting formulas that use OFFSET *which
should work* but don't. I know a thing or two about formulas so I'm
absolutely certain that the formulas I've had problems with *were both
syntactically and logically correct* yet they didn't work when used in CF
but they do work on the worksheet.

The problem seems to be related to the use of OFFSET. This is the only
common factor when this problem arises.

You can get around this by using the INDEX function instead of OFFSET.

I can't figure out what your data setup is like but I replaced your OFFSET
with the equivalent form of INDEX:

=OR(some_test,INDEX(.....)<>"")

And the formatting worked as expected.

 

[Green Biro]

Thank you very much. It does seem like I stumbled into an Excel bug then -
not a very well known one as Google couldn't find it. Anyway, I shall take
the time to rewrite my formulae with INDEX instead of OFFSET and post back
the result.

GB