If then formulas

[Guest]

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â€

 

[Bob Phillips]

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)

 

[Guest]

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]

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)

 

[Guest]

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]

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","current")

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

--
HTH

Bob Phillips

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

 

[Guest]

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

 

[Guest]

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

 

[Bob Phillips]

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(Sheet1!D,MATCH(A1,Sheet1!
A:A,0)),"OK","Changed"),"")

 

[Guest]

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

 

[Guest]

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

 

[Bob Phillips]

You probably only need

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

where A 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)

 

[Guest]

Many thanks Bob, my project is complete.

You probably only need

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

where A 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)