Conditional formatting does not work with MATCH / VLOOKUP

I do have two sheets and use conditional formatting.

One sheet works fine.

When I get a value on that sheet the other sheet with MATCH and
VLOOKUP, and use the same conditional formatting on that sheet, CF does
not work at all.

Bart
Excel 2003

AA
Tue Dec 26 07:54:40 CST 2006

I have three conditions, in the mode "Cell Value". When the value match
the 3rd condition, it is indicated according tio that condition. All
other value who should match the 1st and 2nd conditions are not
indicated to their respective conditions.

Bart


On Dec 26, 6:11 pm, "AA Arens" <bartvandon...@gmail.com> wrote:
> I do have two sheets and use conditional formatting.
>
> One sheet works fine.
>
> When I get a value on that sheet the other sheet with MATCH and
> VLOOKUP, and use the same conditional formatting on that sheet, CF does
> not work at all.
>
> Bart
> Excel 2003

Ron
Tue Dec 26 11:52:56 CST 2006

On 26 Dec 2006 05:54:40 -0800, "AA Arens" <bartvandongen@gmail.com> wrote:

>I have three conditions, in the mode "Cell Value". When the value match
>the 3rd condition, it is indicated according tio that condition. All
>other value who should match the 1st and 2nd conditions are not
>indicated to their respective conditions.
>
>Bart

Given the limited information you supply, diagnosis and treatment is difficult.

Most likely your formulas are not doing what you expect; or your data is not
what your formulas expect (rounding can do this, for example).

If that does not point you in the desired direction, you will need to supply
more information.
--ron

AA
Tue Dec 26 19:35:33 CST 2006

This is the formulah in the cells:
All values looked up are whole values.

=IF(ISNUMBER(MATCH(C7,CTI!$E$7:$E$206,0)),VLOOKUP(C7,CTI!$E$7:$O$206,11,0),"")&
IF(ISNUMBER(MATCH(C7,'Batavia Air'!$E$7:$E$206,0)),VLOOKUP(C7,'Batavia
Air'!$E$7:$O$206,11,0),"")&
IF(ISNUMBER(MATCH(C7,'Multi
Structure'!$E$7:$E$206,0)),VLOOKUP(C7,'Multi
Structure'!$E$7:$O$206,11,0),"")&
IF(ISNUMBER(MATCH(C7,'PEC Tech'!$E$7:$E$206,0)),VLOOKUP(C7,'PEC
Tech'!$E$7:$O$206,11,0),"")&
IF(ISNUMBER(MATCH(C7,Adhimix!$E$7:$E$206,0)),VLOOKUP(C7,Adhimix!$E$7:$O$206,11,0),"")&
IF(ISNUMBER(MATCH(C7,JCB!$E$7:$E$206,0)),VLOOKUP(C7,JCB!$E$7:$O$206,11,0),"")

Bart

The conditions are:

1. value 28-14 (green text)
2. <= 14 (orange text)
3. if value "Expired" (bold red)

The latter is formatted correctly, not the formats 1 and two. While in
the other sheets these cvalues are formatted well.



On Dec 27, 12:52 am, Ron Rosenfeld <ronrosenf...@nospam.org> wrote:
> On 26 Dec 2006 05:54:40 -0800, "AAArens" <bartvandon...@gmail.com> wrote:
>
> >I have three conditions, in the mode "Cell Value". When the value match
> >the 3rd condition, it is indicated according tio that condition. All
> >other value who should match the 1st and 2nd conditions are not
> >indicated to their respective conditions.
>
> >BartGiven the limited information you supply, diagnosis and treatment is difficult.
>
> Most likely your formulas are not doing what you expect; or your data is not
> what your formulas expect (rounding can do this, for example).
>
> If that does not point you in the desired direction, you will need to supply
> more information.
> --ron

Ron
Tue Dec 26 20:08:42 CST 2006

On 26 Dec 2006 17:35:33 -0800, "AA Arens" <bartvandongen@gmail.com> wrote:

>This is the formulah in the cells:
>All values looked up are whole values.
>
>=IF(ISNUMBER(MATCH(C7,CTI!$E$7:$E$206,0)),VLOOKUP(C7,CTI!$E$7:$O$206,11,0),"")&
>IF(ISNUMBER(MATCH(C7,'Batavia Air'!$E$7:$E$206,0)),VLOOKUP(C7,'Batavia
>Air'!$E$7:$O$206,11,0),"")&
>IF(ISNUMBER(MATCH(C7,'Multi
>Structure'!$E$7:$E$206,0)),VLOOKUP(C7,'Multi
>Structure'!$E$7:$O$206,11,0),"")&
>IF(ISNUMBER(MATCH(C7,'PEC Tech'!$E$7:$E$206,0)),VLOOKUP(C7,'PEC
>Tech'!$E$7:$O$206,11,0),"")&
>IF(ISNUMBER(MATCH(C7,Adhimix!$E$7:$E$206,0)),VLOOKUP(C7,Adhimix!$E$7:$O$206,11,0),"")&
>IF(ISNUMBER(MATCH(C7,JCB!$E$7:$E$206,0)),VLOOKUP(C7,JCB!$E$7:$O$206,11,0),"")
>
>Bart
>
>The conditions are:
>
>1. value 28-14 (green text)
>2. <= 14 (orange text)
>3. if value "Expired" (bold red)
>
>The latter is formatted correctly, not the formats 1 and two. While in
>the other sheets these cvalues are formatted well.
>
>
>

It looks to me as if your formula is returning TEXT (a string of numbers), and
the conditional test for values that you are using requires a NUMERIC value.
Concatenation is a string function and returns a string.

From HELP:

& (ampersand): Connects, or concatenates, two values to produce one
continuous text value ("North"&"wind")

The fact that you are concatenating numeric values does not somehow change them
from text to numbers.

Depending on your requirements, you could either test for strings of numbers in
the conditional formatting equations; or coerce the result to be numeric with a
double unary:

=--(your_long_formula)


--ron