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

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

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

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

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

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

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

Bob thanks

Greg
"Bob Phillips" wrote in message
...
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