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"