Old to New Pricing
 

Hello,

I spend so much time changing prices to which thier should be a formula
for.

My problem is sheet 1 has all parts that a supplier offers (20,000) and
sheet 2 is just the parts i take from them (11,000). When they change
thier prices i have to search sheet 1 for the part number then copy the
new price and paste it back into sheet 2. Ovioulsy this takes great
time.

Is there a formula (or way) that i can do this quicker.?

What i need is a way of typing "If sheet2A1 matches sheet1A? then
sheet2 B1=Sheet1 B?"


--
Jennings
------------------------------------------------------------------------
Jennings's Profile: http://www.excelforum.com/member.php...o&userid=31466
View this thread: http://www.excelforum.com/showthread...hreadid=511484

Old to New Pricing
 

If I understand your question properly, you can use the following
formula in Sheet2!B1.

=IF(Sheet2!A1=Sheet1!A1,Sheet1!B1,"")


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com



"Jennings"
wrote in
message
...

Hello,

I spend so much time changing prices to which thier should be a
formula
for.

My problem is sheet 1 has all parts that a supplier offers
(20,000) and
sheet 2 is just the parts i take from them (11,000). When they
change
thier prices i have to search sheet 1 for the part number then
copy the
new price and paste it back into sheet 2. Ovioulsy this takes
great
time.

Is there a formula (or way) that i can do this quicker.?

What i need is a way of typing "If sheet2A1 matches sheet1A?
then
sheet2 B1=Sheet1 B?"


--
Jennings
------------------------------------------------------------------------
Jennings's Profile:
http://www.excelforum.com/member.php...o&userid=31466
View this thread:
http://www.excelforum.com/showthread...hreadid=511484

Old to New Pricing
 

=IF(ISNUMBER(MATCH(Sheet2!A1,Sheet1A:A,0)),INDEX(S heet2!B:B,MATCH(Sheet2!A1,
Sheet1A:A,0)),"")

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"Jennings" wrote in
message ...

Hello,

I spend so much time changing prices to which thier should be a formula
for.

My problem is sheet 1 has all parts that a supplier offers (20,000) and
sheet 2 is just the parts i take from them (11,000). When they change
thier prices i have to search sheet 1 for the part number then copy the
new price and paste it back into sheet 2. Ovioulsy this takes great
time.

Is there a formula (or way) that i can do this quicker.?

What i need is a way of typing "If sheet2A1 matches sheet1A? then
sheet2 B1=Sheet1 B?"


--
Jennings
------------------------------------------------------------------------
Jennings's Profile:
http://www.excelforum.com/member.php...o&userid=31466
View this thread: http://www.excelforum.com/showthread...hreadid=511484

Old to New Pricing
 

Chip your version works but only if i data sort both sheets by column A
Then some are missing because my sheet2 does not have all the part
numbers that are on sheet1.

I.e sheet1 goes in order 1,2,3,4,5,6,7,8 to 50 sheet2 is
2,3,1,6,7,8,41,50
cell B on sheet2 will only work for 2,3 cos they are in the same order
as sheet1 they others are just blank



Bob, your version leaves cell B blank? (as if it does not work)


--
Jennings
------------------------------------------------------------------------
Jennings's Profile: http://www.excelforum.com/member.php...o&userid=31466
View this thread: http://www.excelforum.com/showthread...hreadid=511484

Old to New Pricing
 

Sorry, typos.

=IF(ISNUMBER(MATCH(Sheet2!A1,Sheet1!A:A,0)),INDEX( Sheet1!B:B,MATCH(Sheet2!A1
,Sheet1!A:A,0)),"")

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"Jennings" wrote in
message ...

Chip your version works but only if i data sort both sheets by column A
Then some are missing because my sheet2 does not have all the part
numbers that are on sheet1.

I.e sheet1 goes in order 1,2,3,4,5,6,7,8 to 50 sheet2 is
2,3,1,6,7,8,41,50
cell B on sheet2 will only work for 2,3 cos they are in the same order
as sheet1 they others are just blank



Bob, your version leaves cell B blank? (as if it does not work)


--
Jennings
------------------------------------------------------------------------
Jennings's Profile:
http://www.excelforum.com/member.php...o&userid=31466
View this thread: http://www.excelforum.com/showthread...hreadid=511484

Old to New Pricing
 

Bob,

You are a legend! Thanks that has saved me months of work..


--
Jennings
------------------------------------------------------------------------
Jennings's Profile: http://www.excelforum.com/member.php...o&userid=31466
View this thread: http://www.excelforum.com/showthread...hreadid=511484

Old to New Pricing
 

Wow, makes it worthwhile <G

Bob

"Jennings" wrote in
message ...

Bob,

You are a legend! Thanks that has saved me months of work..


--
Jennings
------------------------------------------------------------------------
Jennings's Profile:
http://www.excelforum.com/member.php...o&userid=31466
View this thread: http://www.excelforum.com/showthread...hreadid=511484