Conditional formatting not working with AND

[Khuli]

I have some conditional formatting to check that a value in column CO
does not match the value in column A, and that cell should not be
blank.

If I check against column A using
=CO31<>OFFSET(CO31,0,1-COLUMN(CO31))
it works fine.

If I check the cell isn't blank using
=CO31<>""
it works fine.

BUT if I combine both and use
=AND(CO31<>OFFSET(CO31,0,1-COLUMN(CO31)), CO31<>"")
then it doesn't work.

Anyone have any ideas? Thanks.

 

[Jim Rech]

Excellent question. It should work but it doesn't. Fortunately the less
job protecting way of writing the formula does work ;-)

=AND(CO31<>A31,CO31<>"")

You wrote it the way you did to avoid a circ? No need.

 

[Khuli]

Actually I wrote it that way because sections of the spreadsheet get
new cells added.

If you add a new cell above CO31, pushing it down to CO32, the
conditional format formula becomes:

=AND(CO32<>A31,CO32<>"")

which is no longer correct.

Thanks for confirming that the formula I had really ought to work
though!

 

[Ron Rosenfeld]

I have some conditional formatting to check that a value in column CO
does not match the value in column A, and that cell should not be
blank.

If I check against column A using
=CO31<>OFFSET(CO31,0,1-COLUMN(CO31))
it works fine.

If I check the cell isn't blank using
=CO31<>""
it works fine.

BUT if I combine both and use
=AND(CO31<>OFFSET(CO31,0,1-COLUMN(CO31)), CO31<>"")
then it doesn't work.

Anyone have any ideas? Thanks.

I, too, have no idea why it doesn't work. However, this equivalent formula does seem to work, and do what you need:

=(CO31<>OFFSET(CO31,0,1-COLUMN(CO31)))*( CO31<>"")