VLookup for Conditional Formatting
 

All,
I have a list of P&L numbers that belong to a sub-group called CTX. If the
EE's P&L number is part of this subgroup, I want it to be green and bold. If
these P&L numbers were in a nice little group I could use a simple "between"
but alas, that isn't the case.

I have a list and I was thinking that somehow I could use VLookup (set to
False to return only the exact matches) and if my EE's P&L matched one of the
P&L's on my list, it would turn green. I can't seem to get this to work in
either "cell value is" or "formula is" conditions

Is this even possible? Or should I string a bunch of "cell value equals
1130 OR 1170 OR 1200 OR 1210" (for about 40 numbers). At what point would
excel choke on the string?

I know a little VBA so I'm okay going in that direction but the message
about having VBA in the workbook gets very annoying (and scares my end
users). If VBA is the way to go, I'll need help to keep it clean (or
figuring out how to sign it digitally so it seems clean!)

Thanks in advance,
Alicia

VLookup for Conditional Formatting
 

Hi Alicia,
See my page on Conditional Formatting
http://www.mvps.org/dmcritchie/excel/condfmt.htm

In a Worksheet Function you use OR(cond1,cond2,...,cond)
the main problems with formulas are the number of levels and
and OR and AND are only involved in one level. I don't think
you are going to exceed "Specification Limits" (see Help) with
what you've indicated.
---
HTH,
David McRitchie, Microsoft MVP - Excel
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Alicia" wrote in message ...
All,
I have a list of P&L numbers that belong to a sub-group called CTX. If the
EE's P&L number is part of this subgroup, I want it to be green and bold. If
these P&L numbers were in a nice little group I could use a simple "between"
but alas, that isn't the case.

I have a list and I was thinking that somehow I could use VLookup (set to
False to return only the exact matches) and if my EE's P&L matched one of the
P&L's on my list, it would turn green. I can't seem to get this to work in
either "cell value is" or "formula is" conditions

Is this even possible? Or should I string a bunch of "cell value equals
1130 OR 1170 OR 1200 OR 1210" (for about 40 numbers). At what point would
excel choke on the string?

I know a little VBA so I'm okay going in that direction but the message
about having VBA in the workbook gets very annoying (and scares my end
users). If VBA is the way to go, I'll need help to keep it clean (or
figuring out how to sign it digitally so it seems clean!)

Thanks in advance,
Alicia

VLookup for Conditional Formatting
 

Ahh! After much searching, I found the magic formula. I made my table into
a named range on another sheet (called "CTX" and the following formula worked:

=ISNUMBER(MATCH(L3,CTX,0)) Where L3 is the P&L number of the employee, CTX
is the named range and 0 means an exact match.

Thanks for pointing me in the right direction!

Alicia


"David McRitchie" wrote:

Hi Alicia,
See my page on Conditional Formatting
http://www.mvps.org/dmcritchie/excel/condfmt.htm

In a Worksheet Function you use OR(cond1,cond2,...,cond)
the main problems with formulas are the number of levels and
and OR and AND are only involved in one level. I don't think
you are going to exceed "Specification Limits" (see Help) with
what you've indicated.
---
HTH,
David McRitchie, Microsoft MVP - Excel
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Alicia" wrote in message ...
All,
I have a list of P&L numbers that belong to a sub-group called CTX. If the
EE's P&L number is part of this subgroup, I want it to be green and bold. If
these P&L numbers were in a nice little group I could use a simple "between"
but alas, that isn't the case.

I have a list and I was thinking that somehow I could use VLookup (set to
False to return only the exact matches) and if my EE's P&L matched one of the
P&L's on my list, it would turn green. I can't seem to get this to work in
either "cell value is" or "formula is" conditions

Is this even possible? Or should I string a bunch of "cell value equals
1130 OR 1170 OR 1200 OR 1210" (for about 40 numbers). At what point would
excel choke on the string?

I know a little VBA so I'm okay going in that direction but the message
about having VBA in the workbook gets very annoying (and scares my end
users). If VBA is the way to go, I'll need help to keep it clean (or
figuring out how to sign it digitally so it seems clean!)

Thanks in advance,
Alicia