vlookup question
 

I have created a workbook for calculating commissions. I import invoices
from QuickBooks into a template that includes a table with inventory cost.
I need to know when an item that is imported is not on the table. Right now
I use vlookup with the FALSE attributr to find cost and, as it should, this
gives a #N/A error. I would like to instead change text color or the cell
background color in the cell with the incorrect item. I have looked around
and cannot find how to do this. Any help? (I prefer to stay away from VBA
if possible)

Dave

vlookup question
 

To avoid the #NA use something like
=IF(iserror(vlookup(A1, Z$1:AB$100,2,false)),"Not found",vlookup(A1,
Z$1:AB$100,2,false))

You can then use conditional formatting to highlight the cells containing
"Not found".

Tim

--
Tim Williams
Palo Alto, CA


"brutus" wrote in message
.net...
I have created a workbook for calculating commissions. I import invoices
from QuickBooks into a template that includes a table with inventory cost.
I need to know when an item that is imported is not on the table. Right
now
I use vlookup with the FALSE attributr to find cost and, as it should,
this
gives a #N/A error. I would like to instead change text color or the cell
background color in the cell with the incorrect item. I have looked
around
and cannot find how to do this. Any help? (I prefer to stay away from
VBA
if possible)

Dave

vlookup question
 

Thanks for your reply. I am familiar with that option when I do a vlookup.
What I am trying to do is flag cells that contain data that is not in the
table. Maybe I need to approach it another way and compare cell contents
with allowable enreies using MATCH?

Dave

"Tim Williams" <timjwilliams at gmail dot com wrote in message
...
To avoid the #NA use something like
=IF(iserror(vlookup(A1, Z$1:AB$100,2,false)),"Not found",vlookup(A1,
Z$1:AB$100,2,false))

You can then use conditional formatting to highlight the cells containing
"Not found".

Tim

--
Tim Williams
Palo Alto, CA


"brutus" wrote in message
.net...
I have created a workbook for calculating commissions. I import invoices
from QuickBooks into a template that includes a table with inventory
cost.
I need to know when an item that is imported is not on the table. Right
now
I use vlookup with the FALSE attributr to find cost and, as it should,
this
gives a #N/A error. I would like to instead change text color or the
cell
background color in the cell with the incorrect item. I have looked
around
and cannot find how to do this. Any help? (I prefer to stay away from
VBA
if possible)

Dave

vlookup question
 

Yes, use conditional formatting and set the "formula" to something
like:

=ISERROR(MATCH(A2,VALS,0))

Where A2 is the cell containing the value and VALS is a named range
with the list of allowed values.

Tim.


"brutus" wrote in message
ink.net...
Thanks for your reply. I am familiar with that option when I do a
vlookup. What I am trying to do is flag cells that contain data that
is not in the table. Maybe I need to approach it another way and
compare cell contents with allowable enreies using MATCH?

Dave

"Tim Williams" <timjwilliams at gmail dot com wrote in message
...
To avoid the #NA use something like
=IF(iserror(vlookup(A1, Z$1:AB$100,2,false)),"Not
found",vlookup(A1,
Z$1:AB$100,2,false))

You can then use conditional formatting to highlight the cells
containing
"Not found".

Tim

--
Tim Williams
Palo Alto, CA


"brutus" wrote in message
.net...
I have created a workbook for calculating commissions. I import
invoices
from QuickBooks into a template that includes a table with
inventory cost.
I need to know when an item that is imported is not on the table.
Right
now
I use vlookup with the FALSE attributr to find cost and, as it
should,
this
gives a #N/A error. I would like to instead change text color or
the cell
background color in the cell with the incorrect item. I have
looked
around
and cannot find how to do this. Any help? (I prefer to stay away
from
VBA
if possible)

Dave

vlookup question
 

That made it work. Thanks a lot.

Dave

"Tim Williams" <saxifrax@pacbell*dot*net wrote in message
...
Yes, use conditional formatting and set the "formula" to something like:

=ISERROR(MATCH(A2,VALS,0))

Where A2 is the cell containing the value and VALS is a named range with
the list of allowed values.

Tim.


"brutus" wrote in message
ink.net...
Thanks for your reply. I am familiar with that option when I do a
vlookup. What I am trying to do is flag cells that contain data that is
not in the table. Maybe I need to approach it another way and compare
cell contents with allowable enreies using MATCH?

Dave

"Tim Williams" <timjwilliams at gmail dot com wrote in message
...
To avoid the #NA use something like
=IF(iserror(vlookup(A1, Z$1:AB$100,2,false)),"Not found",vlookup(A1,
Z$1:AB$100,2,false))

You can then use conditional formatting to highlight the cells
containing
"Not found".

Tim

--
Tim Williams
Palo Alto, CA


"brutus" wrote in message
.net...
I have created a workbook for calculating commissions. I import
invoices
from QuickBooks into a template that includes a table with inventory
cost.
I need to know when an item that is imported is not on the table. Right
now
I use vlookup with the FALSE attributr to find cost and, as it should,
this
gives a #N/A error. I would like to instead change text color or the
cell
background color in the cell with the incorrect item. I have looked
around
and cannot find how to do this. Any help? (I prefer to stay away from
VBA
if possible)

Dave