I need a formula to identify if a cell is missing on either worksheet or if
they are the same. How do you write a formula that states the following:

If A1 on worksheet 2 = A1 on worksheet 1 then €œcurrent€� if not then €œnew€�
AND if A1 on worksheet 1 does not equal A1 on worksheet 2 then €œdisposed€�

What's the difference between

A1 on worksheet 2 not equal to A1 on worksheet 1 i.e. new

and

if A1 on worksheet 1 does not equal A1 on worksheet 2 i.e. disposed

they seem the same condition to me.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"K" wrote in message
...
I need a formula to identify if a cell is missing on either worksheet or
if
they are the same. How do you write a formula that states the following:

If A1 on worksheet 2 = A1 on worksheet 1 then "current" if not then "new"
AND if A1 on worksheet 1 does not equal A1 on worksheet 2 then "disposed"

Worksheet one has an asset listing from 2005 and worksheet 2 has an asset
listing from 2006. If there is an asset on worksheet one (2005) and not on
worksheet two (2006) then an asset has been disposed. If its vice versa (an
asset in 2006 but not for 2005) its new and if it is on both sheets then it
is current. The assets are identifed by numbers.
Does this help?
Many thanks,
K

"Bob Phillips" wrote:

What's the difference between

A1 on worksheet 2 not equal to A1 on worksheet 1 i.e. new

and

if A1 on worksheet 1 does not equal A1 on worksheet 2 i.e. disposed

they seem the same condition to me.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"K" wrote in message
...
I need a formula to identify if a cell is missing on either worksheet or
if
they are the same. How do you write a formula that states the following:

If A1 on worksheet 2 = A1 on worksheet 1 then "current" if not then "new"
AND if A1 on worksheet 1 does not equal A1 on worksheet 2 then "disposed"

But Sheet1!A1<Sheet2!A1 is exactly the same as Sheet2!A1<Sheet1!A1

Or are you saying if the value on Sheet1 and cell A1 is not in a list on
Sheet2 column A, and vice versa.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"K" wrote in message
...
Worksheet one has an asset listing from 2005 and worksheet 2 has an asset
listing from 2006. If there is an asset on worksheet one (2005) and not on
worksheet two (2006) then an asset has been disposed. If it's vice versa
(an
asset in 2006 but not for 2005) it's new and if it is on both sheets then
it
is current. The assets are identifed by numbers.
Does this help?
Many thanks,
K

"Bob Phillips" wrote:

What's the difference between

A1 on worksheet 2 not equal to A1 on worksheet 1 i.e. new

and

if A1 on worksheet 1 does not equal A1 on worksheet 2 i.e. disposed

they seem the same condition to me.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"K" wrote in message
...
I need a formula to identify if a cell is missing on either worksheet
or
if
they are the same. How do you write a formula that states the
following:

If A1 on worksheet 2 = A1 on worksheet 1 then "current" if not then
"new"
AND if A1 on worksheet 1 does not equal A1 on worksheet 2 then
"disposed"

My aplolgies - I try to keep my questions brief but then clarity suffers.
Yes, the later is right.
I have a workbook with 2 worksheets - worksheet 1 has a list of company
assets in 2005 and worksheet 2 has the 2006 assets. I am looking to identify
changes from one year to the next. New assets would be listed on worksheet 2
(2006) but not 1 (2005), disposed assets would be on worksheet 1(2005) & not
2 (2006). If there is no change between worksheets 1 & 2 then the asset is
current.

Not to add any confusion but the bigger picture is this: ultimately, I want
to have a sheet that just identifies changes from year to year - so I am
looking at using a filter to filter out the rows with no changes. See my
other thread above - "Filter or Look up". this question is relative to one of
the 5 columns.

Thank you for your patience Bob,
K

"Bob Phillips" wrote:

But Sheet1!A1<Sheet2!A1 is exactly the same as Sheet2!A1<Sheet1!A1

Or are you saying if the value on Sheet1 and cell A1 is not in a list on
Sheet2 column A, and vice versa.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"K" wrote in message
...
Worksheet one has an asset listing from 2005 and worksheet 2 has an asset
listing from 2006. If there is an asset on worksheet one (2005) and not on
worksheet two (2006) then an asset has been disposed. If it's vice versa
(an
asset in 2006 but not for 2005) it's new and if it is on both sheets then
it
is current. The assets are identifed by numbers.
Does this help?
Many thanks,
K

"Bob Phillips" wrote:

What's the difference between

A1 on worksheet 2 not equal to A1 on worksheet 1 i.e. new

and

if A1 on worksheet 1 does not equal A1 on worksheet 2 i.e. disposed

they seem the same condition to me.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"K" wrote in message
...
I need a formula to identify if a cell is missing on either worksheet
or
if
they are the same. How do you write a formula that states the
following:

If A1 on worksheet 2 = A1 on worksheet 1 then "current" if not then
"new"
AND if A1 on worksheet 1 does not equal A1 on worksheet 2 then
"disposed"

Let's tackle the first one for now, get you comfortable with that, then move
on.

Worksheet 1, B1 and copy down

=IF(ISNA(MATCH(A1,Sheet2!$A$1:$A$100,0)),"disposed ","current")

Worksheet 2 B1 and copy down

=IF(ISNA(MATCH(A1,Sheet1!$A$1:$A$100,0)),"new","cu rrent")

I would actually use "" instead of "current", to highlight the changes.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"K" wrote in message
...
My aplolgies - I try to keep my questions brief but then clarity suffers.
Yes, the later is right.
I have a workbook with 2 worksheets - worksheet 1 has a list of company
assets in 2005 and worksheet 2 has the 2006 assets. I am looking to
identify
changes from one year to the next. New assets would be listed on worksheet
2
(2006) but not 1 (2005), disposed assets would be on worksheet 1(2005) &
not
2 (2006). If there is no change between worksheets 1 & 2 then the asset is
current.

Not to add any confusion but the bigger picture is this: ultimately, I
want
to have a sheet that just identifies changes from year to year - so I am
looking at using a filter to filter out the rows with no changes. See my
other thread above - "Filter or Look up". this question is relative to one
of
the 5 columns.

Thank you for your patience Bob,
K

"Bob Phillips" wrote:

But Sheet1!A1<Sheet2!A1 is exactly the same as Sheet2!A1<Sheet1!A1

Or are you saying if the value on Sheet1 and cell A1 is not in a list on
Sheet2 column A, and vice versa.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"K" wrote in message
...
Worksheet one has an asset listing from 2005 and worksheet 2 has an
asset
listing from 2006. If there is an asset on worksheet one (2005) and
not on
worksheet two (2006) then an asset has been disposed. If it's vice
versa
(an
asset in 2006 but not for 2005) it's new and if it is on both sheets
then
it
is current. The assets are identifed by numbers.
Does this help?
Many thanks,
K

"Bob Phillips" wrote:

What's the difference between

A1 on worksheet 2 not equal to A1 on worksheet 1 i.e. new

and

if A1 on worksheet 1 does not equal A1 on worksheet 2 i.e. disposed

they seem the same condition to me.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"K" wrote in message
...
I need a formula to identify if a cell is missing on either
worksheet
or
if
they are the same. How do you write a formula that states the
following:

If A1 on worksheet 2 = A1 on worksheet 1 then "current" if not
then
"new"
AND if A1 on worksheet 1 does not equal A1 on worksheet 2 then
"disposed"

That works perfectly Bob.
However, is there anyway to get both formulas on worksheet 2 (for 2006)?
Thanks much,
K

"Bob Phillips" wrote:

Let's tackle the first one for now, get you comfortable with that, then move
on.

Worksheet 1, B1 and copy down

=IF(ISNA(MATCH(A1,Sheet2!$A$1:$A$100,0)),"disposed ","current")

Worksheet 2 B1 and copy down

=IF(ISNA(MATCH(A1,Sheet1!$A$1:$A$100,0)),"new","cu rrent")

I would actually use "" instead of "current", to highlight the changes.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"K" wrote in message
...
My aplolgies - I try to keep my questions brief but then clarity suffers.
Yes, the later is right.
I have a workbook with 2 worksheets - worksheet 1 has a list of company
assets in 2005 and worksheet 2 has the 2006 assets. I am looking to
identify
changes from one year to the next. New assets would be listed on worksheet
2
(2006) but not 1 (2005), disposed assets would be on worksheet 1(2005) &
not
2 (2006). If there is no change between worksheets 1 & 2 then the asset is
current.

Not to add any confusion but the bigger picture is this: ultimately, I
want
to have a sheet that just identifies changes from year to year - so I am
looking at using a filter to filter out the rows with no changes. See my
other thread above - "Filter or Look up". this question is relative to one
of
the 5 columns.

Thank you for your patience Bob,
K

"Bob Phillips" wrote:

But Sheet1!A1<Sheet2!A1 is exactly the same as Sheet2!A1<Sheet1!A1

Or are you saying if the value on Sheet1 and cell A1 is not in a list on
Sheet2 column A, and vice versa.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"K" wrote in message
...
Worksheet one has an asset listing from 2005 and worksheet 2 has an
asset
listing from 2006. If there is an asset on worksheet one (2005) and
not on
worksheet two (2006) then an asset has been disposed. If it's vice
versa
(an
asset in 2006 but not for 2005) it's new and if it is on both sheets
then
it
is current. The assets are identifed by numbers.
Does this help?
Many thanks,
K

"Bob Phillips" wrote:

What's the difference between

A1 on worksheet 2 not equal to A1 on worksheet 1 i.e. new

and

if A1 on worksheet 1 does not equal A1 on worksheet 2 i.e. disposed

they seem the same condition to me.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"K" wrote in message
...
I need a formula to identify if a cell is missing on either
worksheet
or
if
they are the same. How do you write a formula that states the
following:

If A1 on worksheet 2 = A1 on worksheet 1 then "current" if not
then
"new"
AND if A1 on worksheet 1 does not equal A1 on worksheet 2 then
"disposed"

Actually Bob - I think I have a remedy for getting these results to one sheet.
I have another question that ties into the formula you provided below. For
the assets that are on both schedules (not new, not disposed but 'current' or
"") I want to make sure the price, for example, is the same for both years.
Each row on both spreadsheets represents an asset - the columns in the rows
represent aspects of each asset - for this example lets say column D is price.
I would like to have a formula to match asset A1 on worksheet 2 (2006) to
Asset A1 on worksheet 1 (2005) then looks to cell D1 (price) in the assets'
row on both sheets and states 'changed' if the value for price is not the
same for both years.

I want to make sure my local controllers aren't changing the price of assets
from year to year to manipulate their profit figures.

Thanks in advance Bob,
K


"K" wrote:

That works perfectly Bob.
However, is there anyway to get both formulas on worksheet 2 (for 2006)?
Thanks much,
K

"Bob Phillips" wrote:

Let's tackle the first one for now, get you comfortable with that, then move
on.

Worksheet 1, B1 and copy down

=IF(ISNA(MATCH(A1,Sheet2!$A$1:$A$100,0)),"disposed ","current")

Worksheet 2 B1 and copy down

=IF(ISNA(MATCH(A1,Sheet1!$A$1:$A$100,0)),"new","cu rrent")

I would actually use "" instead of "current", to highlight the changes.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"K" wrote in message
...
My aplolgies - I try to keep my questions brief but then clarity suffers.
Yes, the later is right.
I have a workbook with 2 worksheets - worksheet 1 has a list of company
assets in 2005 and worksheet 2 has the 2006 assets. I am looking to
identify
changes from one year to the next. New assets would be listed on worksheet
2
(2006) but not 1 (2005), disposed assets would be on worksheet 1(2005) &
not
2 (2006). If there is no change between worksheets 1 & 2 then the asset is
current.

Not to add any confusion but the bigger picture is this: ultimately, I
want
to have a sheet that just identifies changes from year to year - so I am
looking at using a filter to filter out the rows with no changes. See my
other thread above - "Filter or Look up". this question is relative to one
of
the 5 columns.

Thank you for your patience Bob,
K

"Bob Phillips" wrote:

But Sheet1!A1<Sheet2!A1 is exactly the same as Sheet2!A1<Sheet1!A1

Or are you saying if the value on Sheet1 and cell A1 is not in a list on
Sheet2 column A, and vice versa.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"K" wrote in message
...
Worksheet one has an asset listing from 2005 and worksheet 2 has an
asset
listing from 2006. If there is an asset on worksheet one (2005) and
not on
worksheet two (2006) then an asset has been disposed. If it's vice
versa
(an
asset in 2006 but not for 2005) it's new and if it is on both sheets
then
it
is current. The assets are identifed by numbers.
Does this help?
Many thanks,
K

"Bob Phillips" wrote:

What's the difference between

A1 on worksheet 2 not equal to A1 on worksheet 1 i.e. new

and

if A1 on worksheet 1 does not equal A1 on worksheet 2 i.e. disposed

they seem the same condition to me.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"K" wrote in message
...
I need a formula to identify if a cell is missing on either
worksheet
or
if
they are the same. How do you write a formula that states the
following:

If A1 on worksheet 2 = A1 on worksheet 1 then "current" if not
then
"new"
AND if A1 on worksheet 1 does not equal A1 on worksheet 2 then
"disposed"

"K" wrote in message
...
Actually Bob - I think I have a remedy for getting these results to one
sheet.


Can you share that with us?


I have another question that ties into the formula you provided below. For
the assets that are on both schedules (not new, not disposed but 'current'
or
"") I want to make sure the price, for example, is the same for both
years.
Each row on both spreadsheets represents an asset - the columns in the
rows
represent aspects of each asset - for this example lets say column D is
price.
I would like to have a formula to match asset A1 on worksheet 2 (2006) to
Asset A1 on worksheet 1 (2005) then looks to cell D1 (price) in the
assets'
row on both sheets and states 'changed' if the value for price is not the
same for both years.

On sheet 2

=IF(ISNUMBER(MATCH(A1,Sheet1!A:A,0)),IF(D1=INDEX(S heet1!D:D,MATCH(A1,Sheet1!
A:A,0)),"OK","Changed"),"")

I am working on advanced filter & copy to a new worksheet... I will let you
know if I figure something out.
Thanks so much for your help Bob - I will keep you posted,
K

"Bob Phillips" wrote:


"K" wrote in message
...
Actually Bob - I think I have a remedy for getting these results to one
sheet.


Can you share that with us?


I have another question that ties into the formula you provided below. For
the assets that are on both schedules (not new, not disposed but 'current'
or
"") I want to make sure the price, for example, is the same for both
years.
Each row on both spreadsheets represents an asset - the columns in the
rows
represent aspects of each asset - for this example lets say column D is
price.
I would like to have a formula to match asset A1 on worksheet 2 (2006) to
Asset A1 on worksheet 1 (2005) then looks to cell D1 (price) in the
assets'
row on both sheets and states 'changed' if the value for price is not the
same for both years.

On sheet 2

=IF(ISNUMBER(MATCH(A1,Sheet1!A:A,0)),IF(D1=INDEX(S heet1!D:D,MATCH(A1,Sheet1!
A:A,0)),"OK","Changed"),"")

Bob, I ended up creating a separate column at the end named REPORT to
identify if any of the selected columns had "changes" and then filtered the
REPORTcolumn. Here was that formula
=IF(OR(I3="changed",L3="changed",O3="changed",R3=" new",S3="disposed"),"Report","")

It was a MUCH simpler answer - I will gladly send you the workbook if you
are interested.

I have another quick formula question if you have the time - same spreadsheet:
I want a formula similiar to the one you provided below. I would like the
formula to match the asset numbers and then simply return the 'price'.

I played with the formula you provided below but 'nested' formulas are new
to me... and troublesome. I appreciate your patience.
Many thanks,
K

"K" wrote:

I am working on advanced filter & copy to a new worksheet... I will let you
know if I figure something out.
Thanks so much for your help Bob - I will keep you posted,
K

"Bob Phillips" wrote:


"K" wrote in message
...
Actually Bob - I think I have a remedy for getting these results to one
sheet.


Can you share that with us?


I have another question that ties into the formula you provided below. For
the assets that are on both schedules (not new, not disposed but 'current'
or
"") I want to make sure the price, for example, is the same for both
years.
Each row on both spreadsheets represents an asset - the columns in the
rows
represent aspects of each asset - for this example lets say column D is
price.
I would like to have a formula to match asset A1 on worksheet 2 (2006) to
Asset A1 on worksheet 1 (2005) then looks to cell D1 (price) in the
assets'
row on both sheets and states 'changed' if the value for price is not the
same for both years.

On sheet 2

=IF(ISNUMBER(MATCH(A1,Sheet1!A:A,0)),IF(D1=INDEX(S heet1!D:D,MATCH(A1,Sheet1!
A:A,0)),"OK","Changed"),"")

You probably only need

=VLOOKUP(asset_num,$A$1:$D$100,3,False)

where A:D is the table of assets and prices in column C (the ,3 part).

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"K" wrote in message
...
Bob, I ended up creating a separate column at the end named REPORT to
identify if any of the selected columns had "changes" and then filtered
the
REPORTcolumn. Here was that formula

=IF(OR(I3="changed",L3="changed",O3="changed",R3=" new",S3="disposed"),"Repor
t","")

It was a MUCH simpler answer - I will gladly send you the workbook if you
are interested.

I have another quick formula question if you have the time - same
spreadsheet:
I want a formula similiar to the one you provided below. I would like the
formula to match the asset numbers and then simply return the 'price'.

I played with the formula you provided below but 'nested' formulas are new
to me... and troublesome. I appreciate your patience.
Many thanks,
K

"K" wrote:

I am working on advanced filter & copy to a new worksheet... I will let
you
know if I figure something out.
Thanks so much for your help Bob - I will keep you posted,
K

"Bob Phillips" wrote:


"K" wrote in message
...
Actually Bob - I think I have a remedy for getting these results to
one
sheet.


Can you share that with us?


I have another question that ties into the formula you provided
below. For
the assets that are on both schedules (not new, not disposed but
'current'
or
"") I want to make sure the price, for example, is the same for both
years.
Each row on both spreadsheets represents an asset - the columns in
the
rows
represent aspects of each asset - for this example lets say column D
is
price.
I would like to have a formula to match asset A1 on worksheet 2
(2006) to
Asset A1 on worksheet 1 (2005) then looks to cell D1 (price) in the
assets'
row on both sheets and states 'changed' if the value for price is
not the
same for both years.

On sheet 2


=IF(ISNUMBER(MATCH(A1,Sheet1!A:A,0)),IF(D1=INDEX(S heet1!D:D,MATCH(A1,Sheet1!
A:A,0)),"OK","Changed"),"")

Many thanks Bob, my project is complete.

"Bob Phillips" wrote:

You probably only need

=VLOOKUP(asset_num,$A$1:$D$100,3,False)

where A:D is the table of assets and prices in column C (the ,3 part).

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"K" wrote in message
...
Bob, I ended up creating a separate column at the end named REPORT to
identify if any of the selected columns had "changes" and then filtered
the
REPORTcolumn. Here was that formula

=IF(OR(I3="changed",L3="changed",O3="changed",R3=" new",S3="disposed"),"Repor
t","")

It was a MUCH simpler answer - I will gladly send you the workbook if you
are interested.

I have another quick formula question if you have the time - same
spreadsheet:
I want a formula similiar to the one you provided below. I would like the
formula to match the asset numbers and then simply return the 'price'.

I played with the formula you provided below but 'nested' formulas are new
to me... and troublesome. I appreciate your patience.
Many thanks,
K

"K" wrote:

I am working on advanced filter & copy to a new worksheet... I will let
you
know if I figure something out.
Thanks so much for your help Bob - I will keep you posted,
K

"Bob Phillips" wrote:


"K" wrote in message
...
Actually Bob - I think I have a remedy for getting these results to
one
sheet.


Can you share that with us?


I have another question that ties into the formula you provided
below. For
the assets that are on both schedules (not new, not disposed but
'current'
or
"") I want to make sure the price, for example, is the same for both
years.
Each row on both spreadsheets represents an asset - the columns in
the
rows
represent aspects of each asset - for this example lets say column D
is
price.
I would like to have a formula to match asset A1 on worksheet 2
(2006) to
Asset A1 on worksheet 1 (2005) then looks to cell D1 (price) in the
assets'
row on both sheets and states 'changed' if the value for price is
not the
same for both years.

On sheet 2


=IF(ISNUMBER(MATCH(A1,Sheet1!A:A,0)),IF(D1=INDEX(S heet1!D:D,MATCH(A1,Sheet1!
A:A,0)),"OK","Changed"),"")