Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Formatting: Logical AND fails to work with INDIRECT(..
The formula I wish to use for conditional formatting is:
=AND((MOD(COLUMN(),4)=1),(INDIRECT(ADDRESS(ROW(),C OLUMN()))=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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Formatting: Logical AND fails to work withINDIRECT(..
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 On May 14, 3:25*pm, Hamish wrote: The formula I wish to use for conditional formatting is: =AND((MOD(COLUMN(),4)=1),(INDIRECT(ADDRESS(ROW(),C OLUMN()))=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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Formatting: Logical AND fails to work with INDIREC
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,$M 17,$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? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Formatting: Logical AND fails to work with INDIREC
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 On May 14, 6:10*pm, Hamish wrote: 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,$M 17,$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? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Formatting: Logical AND fails to work with INDIREC
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. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Formatting: Logical AND fails to work with INDIREC
You're welcome, Hamish - thanks for feeding back.
Pete On May 15, 2:00*pm, Hamish wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Will Conditional Formatting work? | Excel Worksheet Functions | |||
Will Conditional Formatting work? | Excel Worksheet Functions | |||
Excel Conditional Formatting color multiple cells for 1 logical | Excel Worksheet Functions | |||
Can logical operators be used within Conditional Formatting? | Excel Discussion (Misc queries) | |||
Conditional Formatting Work around? | Excel Worksheet Functions |