Conditional Format based on forumula result

[Hendrik]

I have a column with values, some zero and some greater than zero. These
values are the result of a formula. Whenever the value is higer than 0, the
conditional format wil highlight that value bold & blue.

Now, i've changed the formula with a VLOOKUP. It looks for a value in
another worksheet, if it doesn't find it there, it will use another formula
and use that value.

=IF(P5>0,VLOOKUP(A5,Variance!C:M,10,FALSE),IF(L5<M5,L5,M5))

Now, if the VLOOKUP in this formula returns anything at all, than I want the
cell to be highlighted. I don't know if I can use conditional format for
this. The VLOOKUP might return value 8 for example. but the sencond IF
statement can display 8 as well. If the 8 is a result of the VLOOKUP, i want
the font to change to red. If the second IF statement results in a value
greater than 0, I want the font to change to bold & blue.

Thanks for helping in this seemingly impossible to resolve issue.

 

[Jim Rech]

Conditional Formatting allows up to 3 individual conditions (pre-Excel 2007,
unlimited or at least a lot in Excel 2007), each with its own format (click
the 'add' button). You might consider breaking your formula into two
formulas and putting each in a condition.

--
Jim
|I have a column with values, some zero and some greater than zero. These
| values are the result of a formula. Whenever the value is higer than 0,
the
| conditional format wil highlight that value bold & blue.
|
| Now, i've changed the formula with a VLOOKUP. It looks for a value in
| another worksheet, if it doesn't find it there, it will use another
formula
| and use that value.
|
| =IF(P5>0,VLOOKUP(A5,Variance!C:M,10,FALSE),IF(L5<M5,L5,M5))
|
| Now, if the VLOOKUP in this formula returns anything at all, than I want
the
| cell to be highlighted. I don't know if I can use conditional format for
| this. The VLOOKUP might return value 8 for example. but the sencond IF
| statement can display 8 as well. If the 8 is a result of the VLOOKUP, i
want
| the font to change to red. If the second IF statement results in a value
| greater than 0, I want the font to change to bold & blue.
|
| Thanks for helping in this seemingly impossible to resolve issue.

 

[bpeltzer]

You can do this, but you'll need to use two conditional formats, and you'll
need to switch the first from being being based on the cell's value to being
based on a formula. The first condition should read 'Formula Is'
=not(isna(VLOOKUP(A5,Variance!C:M,10,FALSE))). That will evalutate to TRUE
(and thereby apply the associated condition) when the vlookup finds a match.
The second condition can be what you're using now, 'Cell Value Is' >0, since
the conditions get applied in the order specified.

 

[Hendrik]

Hi Jim,

I can add a conditional format, using the same VLOOKUP as in my formula, so
if the cell has the same value as that VLOOKUP, the conditional format will
kick in. The problem I have is that ocnditional format doesn't allow me to
refer to other worksheets, and my lookup point at another sheet.

Any suggestions?

 

[bpeltzer]

BTW, to get around the restriction on referencing other worksheets, define a
name referencing the table range, then use that name in the vlookup...
Highlight Variance!C:M and Insert > Name > Define and enter a name, say
Lookups. Then the condition formula is
not(isna(VLOOKUP(A5,Lookups,10,FALSE))).

 

[Hendrik]

It works. Thanks guys!

BTW, to get around the restriction on referencing other worksheets, define a
name referencing the table range, then use that name in the vlookup...
Highlight Variance!C:M and Insert > Name > Define and enter a name, say
Lookups. Then the condition formula is
not(isna(VLOOKUP(A5,Lookups,10,FALSE))).