Search list for new addition
 

Good Morning!

I have a list of products that comes from another group. Occassionaly a new
customer is added and they do not inform us - when we run our formulas to
populate the numbers it doesn't populate; however, it is difficult to notice
because there are not always numbers for the products so there are many blank
cells and it just appears that there were no orders. I am looking for a way
to sweep through the list of products and find any products that do not match
my tabled list of current products on the next worksheet.

Any assistance in greatly appreciated. Thank you!
Mac

Mac,

The MATCH function will return an Error if there is no match, so you can
check to see if you new value is in your old table by using a formula like

=IF(ISERROR(MATCH(CellWithNewValue,OldTable,False) ),"This isn't in our
Table!","OK")

where you need to change CellWithNewValue and OldTable to valid cell and
range references, respectively.

HTH,
Bernie
MS Excel MVP

"Mac Landers" wrote in message
...
Good Morning!

I have a list of products that comes from another group. Occassionaly a
new
customer is added and they do not inform us - when we run our formulas to
populate the numbers it doesn't populate; however, it is difficult to
notice
because there are not always numbers for the products so there are many
blank
cells and it just appears that there were no orders. I am looking for a
way
to sweep through the list of products and find any products that do not
match
my tabled list of current products on the next worksheet.

Any assistance in greatly appreciated. Thank you!
Mac

Great, thanks!!

"Bernie Deitrick" wrote:

Mac,

The MATCH function will return an Error if there is no match, so you can
check to see if you new value is in your old table by using a formula like

=IF(ISERROR(MATCH(CellWithNewValue,OldTable,False) ),"This isn't in our
Table!","OK")

where you need to change CellWithNewValue and OldTable to valid cell and
range references, respectively.

HTH,
Bernie
MS Excel MVP

"Mac Landers" wrote in message
...
Good Morning!

I have a list of products that comes from another group. Occassionaly a
new
customer is added and they do not inform us - when we run our formulas to
populate the numbers it doesn't populate; however, it is difficult to
notice
because there are not always numbers for the products so there are many
blank
cells and it just appears that there were no orders. I am looking for a
way
to sweep through the list of products and find any products that do not
match
my tabled list of current products on the next worksheet.

Any assistance in greatly appreciated. Thank you!
Mac

I have one problem as I continue to build this. I have the formula reading
each row (cell with new value) and then it looks it up in my table (old
table). However, the formula copies down exactly for every row - thus all
the rows equal what the first row returns. I do not have it locked with $
and when I try to manually change it, it updates all the cells to equal the
formula I changed. How do I get each row to read the cell value for its row?
Ex. If(ISERROR(Match(D2,$A2:$A29,FALSE)),"This is not in our table!","Ok")
this formula copies all the way down, I want it to be D3, D4, D5 extra.

Thank you!

"Bernie Deitrick" wrote:

Mac,

The MATCH function will return an Error if there is no match, so you can
check to see if you new value is in your old table by using a formula like

=IF(ISERROR(MATCH(CellWithNewValue,OldTable,False) ),"This isn't in our
Table!","OK")

where you need to change CellWithNewValue and OldTable to valid cell and
range references, respectively.

HTH,
Bernie
MS Excel MVP

"Mac Landers" wrote in message
...
Good Morning!

I have a list of products that comes from another group. Occassionaly a
new
customer is added and they do not inform us - when we run our formulas to
populate the numbers it doesn't populate; however, it is difficult to
notice
because there are not always numbers for the products so there are many
blank
cells and it just appears that there were no orders. I am looking for a
way
to sweep through the list of products and find any products that do not
match
my tabled list of current products on the next worksheet.

Any assistance in greatly appreciated. Thank you!
Mac

Mac,

Use this:

=If(ISERROR(Match(D2,$A$2:$A$29,FALSE)),"This is not in our table!","Ok")

Use copy and paste of the cell, not of the cell contents (don't select the
formula from the editing bar). Simply select the cell prior to using Copy,
and the formula should update correctly.

HTH,
Bernie
MS Excel MVP

"Mac Landers" wrote in message
...
I have one problem as I continue to build this. I have the formula
reading
each row (cell with new value) and then it looks it up in my table (old
table). However, the formula copies down exactly for every row - thus all
the rows equal what the first row returns. I do not have it locked with $
and when I try to manually change it, it updates all the cells to equal
the
formula I changed. How do I get each row to read the cell value for its
row?
Ex. If(ISERROR(Match(D2,$A2:$A29,FALSE)),"This is not in our table!","Ok")
this formula copies all the way down, I want it to be D3, D4, D5 extra.

Thank you!

"Bernie Deitrick" wrote:

Mac,

The MATCH function will return an Error if there is no match, so you can
check to see if you new value is in your old table by using a formula
like

=IF(ISERROR(MATCH(CellWithNewValue,OldTable,False) ),"This isn't in our
Table!","OK")

where you need to change CellWithNewValue and OldTable to valid cell and
range references, respectively.

HTH,
Bernie
MS Excel MVP

"Mac Landers" wrote in message
...
Good Morning!

I have a list of products that comes from another group. Occassionaly
a
new
customer is added and they do not inform us - when we run our formulas
to
populate the numbers it doesn't populate; however, it is difficult to
notice
because there are not always numbers for the products so there are
many
blank
cells and it just appears that there were no orders. I am looking for
a
way
to sweep through the list of products and find any products that do
not
match
my tabled list of current products on the next worksheet.

Any assistance in greatly appreciated. Thank you!
Mac

I am up and running. Thank you so much, especially for your quick responses.

"Bernie Deitrick" wrote:

Mac,

Use this:

=If(ISERROR(Match(D2,$A$2:$A$29,FALSE)),"This is not in our table!","Ok")

Use copy and paste of the cell, not of the cell contents (don't select the
formula from the editing bar). Simply select the cell prior to using Copy,
and the formula should update correctly.

HTH,
Bernie
MS Excel MVP

"Mac Landers" wrote in message
...
I have one problem as I continue to build this. I have the formula
reading
each row (cell with new value) and then it looks it up in my table (old
table). However, the formula copies down exactly for every row - thus all
the rows equal what the first row returns. I do not have it locked with $
and when I try to manually change it, it updates all the cells to equal
the
formula I changed. How do I get each row to read the cell value for its
row?
Ex. If(ISERROR(Match(D2,$A2:$A29,FALSE)),"This is not in our table!","Ok")
this formula copies all the way down, I want it to be D3, D4, D5 extra.

Thank you!

"Bernie Deitrick" wrote:

Mac,

The MATCH function will return an Error if there is no match, so you can
check to see if you new value is in your old table by using a formula
like

=IF(ISERROR(MATCH(CellWithNewValue,OldTable,False) ),"This isn't in our
Table!","OK")

where you need to change CellWithNewValue and OldTable to valid cell and
range references, respectively.

HTH,
Bernie
MS Excel MVP

"Mac Landers" wrote in message
...
Good Morning!

I have a list of products that comes from another group. Occassionaly
a
new
customer is added and they do not inform us - when we run our formulas
to
populate the numbers it doesn't populate; however, it is difficult to
notice
because there are not always numbers for the products so there are
many
blank
cells and it just appears that there were no orders. I am looking for
a
way
to sweep through the list of products and find any products that do
not
match
my tabled list of current products on the next worksheet.

Any assistance in greatly appreciated. Thank you!
Mac

Mac,

Glad to hear it, but wait 'til you see my bill ;-)

Bernie
MS Excel MVP

"Mac Landers" wrote in message
...
I am up and running. Thank you so much, especially for your quick
responses.

"Bernie Deitrick" wrote:

Mac,

Use this:

=If(ISERROR(Match(D2,$A$2:$A$29,FALSE)),"This is not in our
table!","Ok")

Use copy and paste of the cell, not of the cell contents (don't select
the
formula from the editing bar). Simply select the cell prior to using
Copy,
and the formula should update correctly.

HTH,
Bernie
MS Excel MVP

"Mac Landers" wrote in message
...
I have one problem as I continue to build this. I have the formula
reading
each row (cell with new value) and then it looks it up in my table
(old
table). However, the formula copies down exactly for every row - thus
all
the rows equal what the first row returns. I do not have it locked
with $
and when I try to manually change it, it updates all the cells to
equal
the
formula I changed. How do I get each row to read the cell value for
its
row?
Ex. If(ISERROR(Match(D2,$A2:$A29,FALSE)),"This is not in our
table!","Ok")
this formula copies all the way down, I want it to be D3, D4, D5
extra.

Thank you!

"Bernie Deitrick" wrote:

Mac,

The MATCH function will return an Error if there is no match, so you
can
check to see if you new value is in your old table by using a
formula
like

=IF(ISERROR(MATCH(CellWithNewValue,OldTable,False) ),"This isn't in
our
Table!","OK")

where you need to change CellWithNewValue and OldTable to valid cell
and
range references, respectively.

HTH,
Bernie
MS Excel MVP

"Mac Landers" wrote in
message
...
Good Morning!

I have a list of products that comes from another group.
Occassionaly
a
new
customer is added and they do not inform us - when we run our
formulas
to
populate the numbers it doesn't populate; however, it is difficult
to
notice
because there are not always numbers for the products so there are
many
blank
cells and it just appears that there were no orders. I am looking
for
a
way
to sweep through the list of products and find any products that
do
not
match
my tabled list of current products on the next worksheet.

Any assistance in greatly appreciated. Thank you!
Mac