Conditional Formatting: Logical AND fails to work with INDIRECT(..

[Hamish]

The formula I wish to use for conditional formatting is
=AND((MOD(COLUMN(),4)=1),(INDIRECT(ADDRESS(ROW(),COLUMN()))=MIN($E17,$I17,$M17,$Q17,$U17)))

If I use:
=INDIRECT(ADDRESS(ROW(),COLUMN()))=MIN($E17,$I17,$M17,$Q17,$U17)
That works correctly. The highest value in the row is highlighted.

If I use:
=AND((MOD(COLUMN(),4)=1),(1=1))
That works correctly. The first and every 5th column is highlighted.

However, if I use:
=AND((1=1),(INDIRECT(ADDRESS(ROW(),COLUMN()))=MIN($E17,$I17,$M17,$Q17,$U17)))
This does not work correctly. Nothing is highlighted.

 

[Pete_UK]

Not sure why you have the (1=1) term in there, or indeed why you have
got INDIRECT(ADDRESS(ROW(),COLUMN())) - why not just use a cell
reference here?

Hope this helps.

Pete

 

[Hamish]

The (1=1) is just a placeholder that is always TRUE, prehaps I should have
used 'TRUE' instead to be clearer.

On its own INDIRECT(ADDRESS(ROW(),COLUMN()))=MIN($E17,$I17,$M17,$Q17,$U17)
equates to true in the smallest value in any of $E17,$I17,$M17,$Q17,$U17 and
that cell is highlighted as it should be.

The point is that as soon as that statement is put inside an AND, it fails
to work.

I am using INDIRECT(ADDRESS(ROW(),COLUMN())) to get the value of each cell
individualy. How would I do this with a cell reference?

 

[Pete_UK]

Imagine you want this to apply to a block of cells A1720. Highlight
those cells with A17 as the active cell, then click on Format |
Conditional Formatting and choose Formula Is in the first box. In the
formula box you can enter:

=A17=MIN($E17,$I17,$M17,$Q17,$U17)

then click on the Format button and choose your format. When you OK
your way out of the dialogue box you will find that the A17 will have
changed to B17, C17, D17, A18, B18, C18, D18 etc in those cells (and
also the 17 in the MIN term will have changed to 18, 19, 20 etc in
successive rows). So, this will highlight the appropriate cell if it
is equal to the minimum of those cells on the corresponding row. I
think this is what you want to happen.

Hope this helps.

Pete

 

[Hamish]

Well thats working fine.

So now that im not using INDIRECT, the AND works perfectly and I can do this:
I now have two rules:
=AND((MOD(COLUMN(),4)=1),(D41=MIN($E41,$I41,$M41,$Q41,$U41)))
and
=AND((MOD(COLUMN(),4)=3),(D41=MAX($G41,$K41,$O41,$S41,$W41)))

It is however strange that the AND breaks down when one tries to pass an
INDIRECT as one of its arguments.

Thanks very much for your help.

 

[Pete_UK]

You're welcome, Hamish - thanks for feeding back.

Pete