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,'20 06 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

On Aug 24, 9:24*pm, Mary Lou
wrote:
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,'20 06 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?

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

Hope this helps.

Pete

On Aug 24, 9:53*pm, Pete_UK wrote:
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

On Aug 24, 9:24*pm, Mary Lou
wrote:



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,'20 06 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?- Hide quoted text -

- Show quoted text -

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

"Pete_UK" wrote:

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

On Aug 24, 9:24 pm, Mary Lou
wrote:
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,'20 06 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?

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

On Aug 24, 10:14*pm, Mary Lou
wrote:
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



"Pete_UK" wrote:
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

On Aug 24, 9:24 pm, Mary Lou
wrote:
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,'20 06 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?- Hide quoted text -

- Show quoted text -

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.

"Pete_UK" wrote:

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

On Aug 24, 10:14 pm, Mary Lou
wrote:
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



"Pete_UK" wrote:
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

On Aug 24, 9:24 pm, Mary Lou
wrote:
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,'20 06 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?- Hide quoted text -

- Show quoted text -

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

On Aug 25, 1:38*am, Mary Lou
wrote:
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.



"Pete_UK" wrote:
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

On Aug 24, 10:14 pm, Mary Lou
wrote:
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

"Pete_UK" wrote:
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

On Aug 24, 9:24 pm, Mary Lou
wrote:
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,'20 06 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?- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -