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

H

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.
 
P

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
 
H

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?
 
P

Pete_UK

Imagine you want this to apply to a block of cells A17:D20. 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
 
H

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top