Old to New Pricing

[Jennings]

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?"

[Chip Pearson]

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"
<[email protected]> wrote in
message
news:[email protected]...
>
> 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
>

[Bob Phillips]

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

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"Jennings" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[Jennings]

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)

[Bob Phillips]

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[Jennings]

Bob,

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

[Bob Phillips]

Wow, makes it worthwhile <G>

Bob

"Jennings" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>