| Home |
| Search |
| Today's Posts |
|
|
|
#1
|
|||
|
|||
Conditional Formating - not in table
I have a worksheet that has many columns, one of which is a model
number. I have conditional formated this column, so that if the model number exists in a table(Supply) on another sheet, it colors the cell. I am using the following formula in CF to get that result: =$C4=vlookup($C4,Supply,1,False) and it works as designed. What I want is the opposite effect, if the item is not found in the Supply table, I want the CF to apply. I have tried: =$C4<vlookup($C4,Supply,1,False)as well as many other combos to no avail. What is the formula. TIA Greg |
|
#2
|
|||
|
|||
|
Try =$C4=isna(vlookup($C4,Supply,1,False))
Duke "GregR" wrote: I have a worksheet that has many columns, one of which is a model number. I have conditional formated this column, so that if the model number exists in a table(Supply) on another sheet, it colors the cell. I am using the following formula in CF to get that result: =$C4=vlookup($C4,Supply,1,False) and it works as designed. What I want is the opposite effect, if the item is not found in the Supply table, I want the CF to apply. I have tried: =$C4<vlookup($C4,Supply,1,False)as well as many other combos to no avail. What is the formula. TIA Greg |
|
#3
|
|||
|
|||
|
Greg,
Try =NOT(ISNUMBER(MATCH($C4,Supply,0))) you can also simplify yours to =ISNUMBER(MATCH($C4,Supply,0)) -- HTH RP (remove nothere from the email address if mailing direct) "GregR" wrote in message ups.com... I have a worksheet that has many columns, one of which is a model number. I have conditional formated this column, so that if the model number exists in a table(Supply) on another sheet, it colors the cell. I am using the following formula in CF to get that result: =$C4=vlookup($C4,Supply,1,False) and it works as designed. What I want is the opposite effect, if the item is not found in the Supply table, I want the CF to apply. I have tried: =$C4<vlookup($C4,Supply,1,False)as well as many other combos to no avail. What is the formula. TIA Greg |
|
#4
|
|||
|
|||
|
Bob -
You know - I gotta stop putting my $.02 in, because you and other MVPs keep offering up suggestions & solutions that put mine to shame. "Bob Phillips" wrote: Greg, Try =NOT(ISNUMBER(MATCH($C4,Supply,0))) you can also simplify yours to =ISNUMBER(MATCH($C4,Supply,0)) -- HTH RP (remove nothere from the email address if mailing direct) "GregR" wrote in message ups.com... I have a worksheet that has many columns, one of which is a model number. I have conditional formated this column, so that if the model number exists in a table(Supply) on another sheet, it colors the cell. I am using the following formula in CF to get that result: =$C4=vlookup($C4,Supply,1,False) and it works as designed. What I want is the opposite effect, if the item is not found in the Supply table, I want the CF to apply. I have tried: =$C4<vlookup($C4,Supply,1,False)as well as many other combos to no avail. What is the formula. TIA Greg |
|
#5
|
|||
|
|||
|
Don't stop, there's always the gem that you spot that would then be missed.
Anyway, it helps show the OP that there is more than one way, and that way they should learn more. Best Regards Bob "Duke Carey" wrote in message ... Bob - You know - I gotta stop putting my $.02 in, because you and other MVPs keep offering up suggestions & solutions that put mine to shame. "Bob Phillips" wrote: Greg, Try =NOT(ISNUMBER(MATCH($C4,Supply,0))) you can also simplify yours to =ISNUMBER(MATCH($C4,Supply,0)) -- HTH RP (remove nothere from the email address if mailing direct) "GregR" wrote in message ups.com... I have a worksheet that has many columns, one of which is a model number. I have conditional formated this column, so that if the model number exists in a table(Supply) on another sheet, it colors the cell. I am using the following formula in CF to get that result: =$C4=vlookup($C4,Supply,1,False) and it works as designed. What I want is the opposite effect, if the item is not found in the Supply table, I want the CF to apply. I have tried: =$C4<vlookup($C4,Supply,1,False)as well as many other combos to no avail. What is the formula. TIA Greg |
|
#6
|
|||
|
|||
|
Bob, as usual, thanks for you tremendous help and Duke, keep them coming.
Bob, just one question, if my model numbers are actually text values, would I change the isnumber to istext. TIA Greg "Bob Phillips" wrote in message ... Don't stop, there's always the gem that you spot that would then be missed. Anyway, it helps show the OP that there is more than one way, and that way they should learn more. Best Regards Bob "Duke Carey" wrote in message ... Bob - You know - I gotta stop putting my $.02 in, because you and other MVPs keep offering up suggestions & solutions that put mine to shame. "Bob Phillips" wrote: Greg, Try =NOT(ISNUMBER(MATCH($C4,Supply,0))) you can also simplify yours to =ISNUMBER(MATCH($C4,Supply,0)) -- HTH RP (remove nothere from the email address if mailing direct) "GregR" wrote in message ups.com... I have a worksheet that has many columns, one of which is a model number. I have conditional formated this column, so that if the model number exists in a table(Supply) on another sheet, it colors the cell. I am using the following formula in CF to get that result: =$C4=vlookup($C4,Supply,1,False) and it works as designed. What I want is the opposite effect, if the item is not found in the Supply table, I want the CF to apply. I have tried: =$C4<vlookup($C4,Supply,1,False)as well as many other combos to no avail. What is the formula. TIA Greg |
|
#7
|
|||
|
|||
|
No, because the ISNUMBER applies to the result of the MATCH, which will
return an index (number) if found, and #N/A if not. ISNUMBER is used to get a True/False value. -- HTH RP (remove nothere from the email address if mailing direct) "GregR" wrote in message ... Bob, as usual, thanks for you tremendous help and Duke, keep them coming. Bob, just one question, if my model numbers are actually text values, would I change the isnumber to istext. TIA Greg "Bob Phillips" wrote in message ... Don't stop, there's always the gem that you spot that would then be missed. Anyway, it helps show the OP that there is more than one way, and that way they should learn more. Best Regards Bob "Duke Carey" wrote in message ... Bob - You know - I gotta stop putting my $.02 in, because you and other MVPs keep offering up suggestions & solutions that put mine to shame. "Bob Phillips" wrote: Greg, Try =NOT(ISNUMBER(MATCH($C4,Supply,0))) you can also simplify yours to =ISNUMBER(MATCH($C4,Supply,0)) -- HTH RP (remove nothere from the email address if mailing direct) "GregR" wrote in message ups.com... I have a worksheet that has many columns, one of which is a model number. I have conditional formated this column, so that if the model number exists in a table(Supply) on another sheet, it colors the cell. I am using the following formula in CF to get that result: =$C4=vlookup($C4,Supply,1,False) and it works as designed. What I want is the opposite effect, if the item is not found in the Supply table, I want the CF to apply. I have tried: =$C4<vlookup($C4,Supply,1,False)as well as many other combos to no avail. What is the formula. TIA Greg |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Conditional Formating on calculated data in a pivot table | Excel Worksheet Functions | |||
| Conditional Formating | Excel Discussion (Misc queries) | |||
| How to preserve conditional formatting on a web query table result | Excel Discussion (Misc queries) | |||
| Help using Conditional Formating of Entire Rows | Excel Worksheet Functions | |||
| more than 3 conditional formating in excel | Excel Discussion (Misc queries) | |||
Hybrid Mode
