Old to New Pricing

J

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

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

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)
 
J

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)
 
B

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)
 
Top