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!