| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional Formating - formula not working
2003
Cell A1 = 101 Cell B1 = 101 Cell C1 = #N/A (Unsuccessful VLookup) What I want (CF background color of A1 = Red) if EITHER B1 or C1 does not equal A1 The actual not-working formula in A1 Conditional Format is: Formula is =OR(A1<B1,A1<C1) Then Set Background Color to Red Thanks EagleOne |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Conditional Formating - formula not working
Both B1 and C1 are not equal to A1 in the example you gave. Therefore the
conditional formatting is not applied. -- Brevity is the soul of wit. "EagleOne" wrote: 2003 Cell A1 = 101 Cell B1 = 101 Cell C1 = #N/A (Unsuccessful VLookup) What I want (CF background color of A1 = Red) if EITHER B1 or C1 does not equal A1 The actual not-working formula in A1 Conditional Format is: Formula is =OR(A1<B1,A1<C1) Then Set Background Color to Red Thanks EagleOne |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Conditional Formating - formula not working
Why not just clean up your vlookup formula so that it no longer returns #NA.
You can do that with an if statement and either CountIf or ISNA something like this... =if(countif(A2:A10, A1)= 0, "Not found", vlookup(A1, A2:B10, 2, False)) or =if(ISNA(vlookup(A1, A2:B10, 2, False)), "Not Found", vlookup(A1, A2:B10, 2, False)) -- HTH... Jim Thomlinson "EagleOne" wrote: 2003 Cell A1 = 101 Cell B1 = 101 Cell C1 = #N/A (Unsuccessful VLookup) What I want (CF background color of A1 = Red) if EITHER B1 or C1 does not equal A1 The actual not-working formula in A1 Conditional Format is: Formula is =OR(A1<B1,A1<C1) Then Set Background Color to Red Thanks EagleOne |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Conditional Formating - formula not working
OK, humor me.
A1 = B1 A1 < C1 Since this is OR, if either (not both) conditions do not equal A1 then the Format should be applied. No? Maybe the formulas work differently when in CF? Dave F wrote: Both B1 and C1 are not equal to A1 in the example you gave. Therefore the conditional formatting is not applied. -- Brevity is the soul of wit. "EagleOne" wrote: 2003 Cell A1 = 101 Cell B1 = 101 Cell C1 = #N/A (Unsuccessful VLookup) What I want (CF background color of A1 = Red) if EITHER B1 or C1 does not equal A1 The actual not-working formula in A1 Conditional Format is: Formula is =OR(A1<B1,A1<C1) Then Set Background Color to Red Thanks EagleOne |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Conditional Formating - formula not working
Using your logic I got the red to work - however I had to remove the double
quotes the conditional formating added when I typed in the equation "EagleOne" wrote: 2003 Cell A1 = 101 Cell B1 = 101 Cell C1 = #N/A (Unsuccessful VLookup) What I want (CF background color of A1 = Red) if EITHER B1 or C1 does not equal A1 The actual not-working formula in A1 Conditional Format is: Formula is =OR(A1<B1,A1<C1) Then Set Background Color to Red Thanks EagleOne |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Conditional Formating - formula not working
Your point is well taken. Even so, I still have the CF issue meaning
that A1 < C1 whether it is #N/A or "Not Found." Did I miss something? Jim Thomlinson wrote: Why not just clean up your vlookup formula so that it no longer returns #NA. You can do that with an if statement and either CountIf or ISNA something like this... =if(countif(A2:A10, A1)= 0, "Not found", vlookup(A1, A2:B10, 2, False)) or =if(ISNA(vlookup(A1, A2:B10, 2, False)), "Not Found", vlookup(A1, A2:B10, 2, False)) -- HTH... Jim Thomlinson "EagleOne" wrote: 2003 Cell A1 = 101 Cell B1 = 101 Cell C1 = #N/A (Unsuccessful VLookup) What I want (CF background color of A1 = Red) if EITHER B1 or C1 does not equal A1 The actual not-working formula in A1 Conditional Format is: Formula is =OR(A1<B1,A1<C1) Then Set Background Color to Red Thanks EagleOne |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Conditional Formating - formula not working
Brad,
The issue appears to be related to the displayed results of an unsuccessful VLookup. Meaning, if I type in #N/A, the CF formula works but if the displayed #N/A is from the VLookup, it is not considered "not equal" and the formula fails. How do I work around this? EagleOne Brad wrote: Using your logic I got the red to work - however I had to remove the double quotes the conditional formating added when I typed in the equation "EagleOne" wrote: 2003 Cell A1 = 101 Cell B1 = 101 Cell C1 = #N/A (Unsuccessful VLookup) What I want (CF background color of A1 = Red) if EITHER B1 or C1 does not equal A1 The actual not-working formula in A1 Conditional Format is: Formula is =OR(A1<B1,A1<C1) Then Set Background Color to Red Thanks EagleOne |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Conditional Formating - formula not working
In the end, your suggestion worked because "Not Found" tripped the CF
formula properly whereas the displayed #N/A from the VLookup did not. Thanks Jim but ..... Do you or does anyone know WHY the formula does not work in that case? EagleOne Jim Thomlinson wrote: Why not just clean up your vlookup formula so that it no longer returns #NA. You can do that with an if statement and either CountIf or ISNA something like this... =if(countif(A2:A10, A1)= 0, "Not found", vlookup(A1, A2:B10, 2, False)) or =if(ISNA(vlookup(A1, A2:B10, 2, False)), "Not Found", vlookup(A1, A2:B10, 2, False)) -- HTH... Jim Thomlinson "EagleOne" wrote: 2003 Cell A1 = 101 Cell B1 = 101 Cell C1 = #N/A (Unsuccessful VLookup) What I want (CF background color of A1 = Red) if EITHER B1 or C1 does not equal A1 The actual not-working formula in A1 Conditional Format is: Formula is =OR(A1<B1,A1<C1) Then Set Background Color to Red Thanks EagleOne |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Conditional Formating - formula not working
Convert the #N/A into a number that is never equal to A, say 0,
with this formula: =IF(ISERROR(Cm),0,Cm) Then plug it into your OR formula like this: =OR(A=B,IF(ISERROR(C),0,C)<A) This is an inclusive OR, defined as "if either term is TRUE", but you specified: "if either term is TRUE, but not both". This is called an exclusive OR and is much more complicated. Google "exclusive OR" The formula then becomes: =OR((A=B)*NOT(IF(ISERROR(C),0,C)<A), NOT(A=B)*(IF(ISERROR(C),0,C)<A)) |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Conditional Formating - formula not working
My suggestion would have been the same as Jim's
=if(ISNA(vlookup(A1, A2:B10, 2, False)), "Not Found", vlookup(A1, A2:B10, 2, False)) -- With one small adjustment =if(ISNA(vlookup(A1, $A$2:$B$10, 2, False)), "Not Found", vlookup(A1, $A$2:$B$10, 2, False)) That way if you need to copy the equation - your lookup range remains the same. Why Excel handled the #NA differently - can't give you a 100% definitive answer. "EagleOne" wrote: Brad, The issue appears to be related to the displayed results of an unsuccessful VLookup. Meaning, if I type in #N/A, the CF formula works but if the displayed #N/A is from the VLookup, it is not considered "not equal" and the formula fails. How do I work around this? EagleOne Brad wrote: Using your logic I got the red to work - however I had to remove the double quotes the conditional formating added when I typed in the equation "EagleOne" wrote: 2003 Cell A1 = 101 Cell B1 = 101 Cell C1 = #N/A (Unsuccessful VLookup) What I want (CF background color of A1 = Red) if EITHER B1 or C1 does not equal A1 The actual not-working formula in A1 Conditional Format is: Formula is =OR(A1<B1,A1<C1) Then Set Background Color to Red Thanks EagleOne |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Conditional Formating - formula not working
Even -OR- is argumentative! (no wonder learning this is challenging).
Thanks for the insight Herbert Seidenberg wrote: Convert the #N/A into a number that is never equal to A, say 0, with this formula: =IF(ISERROR(Cm),0,Cm) Then plug it into your OR formula like this: =OR(A=B,IF(ISERROR(C),0,C)<A) This is an inclusive OR, defined as "if either term is TRUE", but you specified: "if either term is TRUE, but not both". This is called an exclusive OR and is much more complicated. Google "exclusive OR" The formula then becomes: =OR((A=B)*NOT(IF(ISERROR(C),0,C)<A), NOT(A=B)*(IF(ISERROR(C),0,C)<A)) |
|
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Conditional Formating - formula not working
Brad, thanks for your time and knowledge!
Brad wrote: My suggestion would have been the same as Jim's =if(ISNA(vlookup(A1, A2:B10, 2, False)), "Not Found", vlookup(A1, A2:B10, 2, False)) -- With one small adjustment =if(ISNA(vlookup(A1, $A$2:$B$10, 2, False)), "Not Found", vlookup(A1, $A$2:$B$10, 2, False)) That way if you need to copy the equation - your lookup range remains the same. Why Excel handled the #NA differently - can't give you a 100% definitive answer. "EagleOne" wrote: Brad, The issue appears to be related to the displayed results of an unsuccessful VLookup. Meaning, if I type in #N/A, the CF formula works but if the displayed #N/A is from the VLookup, it is not considered "not equal" and the formula fails. How do I work around this? EagleOne Brad wrote: Using your logic I got the red to work - however I had to remove the double quotes the conditional formating added when I typed in the equation "EagleOne" wrote: 2003 Cell A1 = 101 Cell B1 = 101 Cell C1 = #N/A (Unsuccessful VLookup) What I want (CF background color of A1 = Red) if EITHER B1 or C1 does not equal A1 The actual not-working formula in A1 Conditional Format is: Formula is =OR(A1<B1,A1<C1) Then Set Background Color to Red Thanks EagleOne |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Conditional Formating with a formula referencing other cells | Excel Worksheet Functions | |||
| Conditional formating using array formula?? | Excel Discussion (Misc queries) | |||
| Conditional formating | Excel Discussion (Misc queries) | |||
| Formula checking multiple worksheets | Excel Worksheet Functions | |||
| Formula checking multiple worksheets | Excel Worksheet Functions | |||
Linear Mode
