conditional formatting

  • Thread starter Thread starter Mary Lou
  • Start date Start date
M

Mary Lou

i have a vlookup formula as follows:

=IF(ISNA(VLOOKUP($A56,'2006 Top
40'!$A$8:$B$470,2,FALSE)),IF(ISERROR(VLOOKUP($A56,'2006 All
Clients'!$A$7:$B$4482,2,FALSE)),"0",(VLOOKUP($A56,'2006 All
Clients'!$A$7:$B$4482,2,FALSE))),(VLOOKUP($A56,'2006 Top
40'!$A$8:$B518,2,FALSE)))

Now - i would like to add conditional formatting so that if the result came
from the middle vlookup - the font color would be different - is this
possible?
 
You will need to select Formula Is rather than Cell Value Is in the CF
dialogue, and use this formula:

=NOT(ISNA(MATCH($A56,'2006 All Clients'!$A$7:$A$4482,0)))

then choose the background colour you would like for this condition.
This assumes that cell A56 is the active cell when you click on the
Format | Conditional Formatting menu.

Hope this helps.

Pete
 
Sorry, it doesn't matter which column you are in, but you will need to
be on row 56.

Hope this helps.

Pete
 
Here is the criteria i used in conditional formatting:

=NOT(ISNA(MATCH($A8,'2006 All Clients'!$A$7:$A$4482,0)))

i get an error message that says "you may not use references to other
worksheets or workbooks for conditioal formattig criteria"

have i done something wrong?

thanks for your response
 
Well, I should have tried it before posting as I thought that might be
the case, but the error message says it all - you can't use a
reference to another worksheet. However, if you define the named range
Table1 as '2006 All Clients'!$A$7:$A$4482, then you might be able to
use:

=NOT(ISNA(MATCH($A8,Table1,0)))

as your criteria, but again, I haven't tried it myself.

Hope this helps.

Pete
 
Thanks again for your response. i tried it but it looks like the formula
wont work. gonna keep at it though.

have a great night.
 
One way of doing it would be to copy the cells from '2006 All Clients'!
$A$7:$A$4482 into the current worksheet in some out-of-the-way place
(eg column Z), or, if the values are likely to change, you can link to
those cells by putting this in Z7:

='2006 All Clients'!$A7

and copy down to Z4482. Then the first CF formula I gave you could
become:

=NOT(ISNA(MATCH($A56,$Z$7:$Z$4482,0)))

so that you are not refering to another sheet.

Hope this helps.

Pete
 
Back
Top