If then formulas
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"
|