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?

If you want your condition still to be effective, you NEED either
=OR(FALSE,...) or =AND(TRUE,...)

=OR(TRUE,...) will always be TRUE, regardless of whether the rest of the
formula is TRUE or FALSE.
--
David Biddulph

"Green Biro" wrote in message
...
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?

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


"David Biddulph" <groups [at] biddulph.org.uk wrote in message
...
If you want your condition still to be effective, you NEED either
=OR(FALSE,...) or =AND(TRUE,...)

=OR(TRUE,...) will always be TRUE, regardless of whether the rest of the
formula is TRUE or FALSE.
--
David Biddulph

"Green Biro" wrote in message
...
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?

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.

--
Biff
Microsoft Excel MVP


"Green Biro" wrote in message
...
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


"David Biddulph" <groups [at] biddulph.org.uk wrote in message
...
If you want your condition still to be effective, you NEED either
=OR(FALSE,...) or =AND(TRUE,...)

=OR(TRUE,...) will always be TRUE, regardless of whether the rest of the
formula is TRUE or FALSE.
--
David Biddulph

"Green Biro" wrote in message
...
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?

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


"T. Valko" wrote in message
...
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.

--
Biff
Microsoft Excel MVP

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?