software

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 quot;If sheet2A1 matches sheet1A? then
sheet2 B1=Sheet1 B?quot;--
Jennings
------------------------------------------------------------------------
Jennings's Profile: www.excelforum.com/member.php...oamp;userid=31466
View this thread: www.excelforum.com/showthread...hreadid=511484If I understand your question properly, you can use the following
formula in Sheet2!B1.

=IF(Sheet2!A1=Sheet1!A1,Sheet1!B1,quot;quot;)--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
quot;Jenningsquot;
gt; wrote in
message
...
gt;
gt; Hello,
gt;
gt; I spend so much time changing prices to which thier should be a
gt; formula
gt; for.
gt;
gt; My problem is sheet 1 has all parts that a supplier offers
gt; (20,000) and
gt; sheet 2 is just the parts i take from them (11,000). When they
gt; change
gt; thier prices i have to search sheet 1 for the part number then
gt; copy the
gt; new price and paste it back into sheet 2. Ovioulsy this takes
gt; great
gt; time.
gt;
gt; Is there a formula (or way) that i can do this quicker.?
gt;
gt; What i need is a way of typing quot;If sheet2A1 matches sheet1A?
gt; then
gt; sheet2 B1=Sheet1 B?quot;
gt;
gt;
gt; --
gt; Jennings
gt; ------------------------------------------------------------------------
gt; Jennings's Profile:
gt; www.excelforum.com/member.php...oamp;userid=31466
gt; View this thread:
gt; www.excelforum.com/showthread...hreadid=511484
gt;
=IF(ISNUMBER(MATCH(Sheet2!A1,Sheet1A:A,0)),INDEX(S heet2!B:B,MATCH(Sheet2!A1,
Sheet1A:A,0)),quot;quot;)

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

quot;Jenningsquot; gt; wrote in
message ...
gt;
gt; Hello,
gt;
gt; I spend so much time changing prices to which thier should be a formula
gt; for.
gt;
gt; My problem is sheet 1 has all parts that a supplier offers (20,000) and
gt; sheet 2 is just the parts i take from them (11,000). When they change
gt; thier prices i have to search sheet 1 for the part number then copy the
gt; new price and paste it back into sheet 2. Ovioulsy this takes great
gt; time.
gt;
gt; Is there a formula (or way) that i can do this quicker.?
gt;
gt; What i need is a way of typing quot;If sheet2A1 matches sheet1A? then
gt; sheet2 B1=Sheet1 B?quot;
gt;
gt;
gt; --
gt; Jennings
gt; ------------------------------------------------------------------------
gt; Jennings's Profile:
www.excelforum.com/member.php...oamp;userid=31466
gt; View this thread: www.excelforum.com/showthread...hreadid=511484
gt;

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: www.excelforum.com/member.php...oamp;userid=31466
View this thread: www.excelforum.com/showthread...hreadid=511484Sorry, typos.

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

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

quot;Jenningsquot; gt; wrote in
message ...
gt;
gt; Chip your version works but only if i data sort both sheets by column A
gt; Then some are missing because my sheet2 does not have all the part
gt; numbers that are on sheet1.
gt;
gt; I.e sheet1 goes in order 1,2,3,4,5,6,7,8 to 50 sheet2 is
gt; 2,3,1,6,7,8,41,50
gt; cell B on sheet2 will only work for 2,3 cos they are in the same order
gt; as sheet1 they others are just blank
gt;
gt;
gt;
gt; Bob, your version leaves cell B blank? (as if it does not work)
gt;
gt;
gt; --
gt; Jennings
gt; ------------------------------------------------------------------------
gt; Jennings's Profile:
www.excelforum.com/member.php...oamp;userid=31466
gt; View this thread: www.excelforum.com/showthread...hreadid=511484
gt;

Bob,

You are a legend! Thanks that has saved me months of work..--
Jennings
------------------------------------------------------------------------
Jennings's Profile: www.excelforum.com/member.php...oamp;userid=31466
View this thread: www.excelforum.com/showthread...hreadid=511484Wow, makes it worthwhile lt;Ggt;

Bob

quot;Jenningsquot; gt; wrote in
message ...
gt;
gt; Bob,
gt;
gt; You are a legend! Thanks that has saved me months of work..
gt;
gt;
gt; --
gt; Jennings
gt; ------------------------------------------------------------------------
gt; Jennings's Profile:
www.excelforum.com/member.php...oamp;userid=31466
gt; View this thread: www.excelforum.com/showthread...hreadid=511484
gt;