Update rows based on part number, possible?

Hello..

I have been trying to figure out how to do this..

I have a master spreadsheet that contains many part numbers, description, weight, price, ect...

Every month I receive an excel sheet that show what prices have changed, and only those that have changed.

Is there a way I can have excel change only the rows that match up to the part numbers that change?

Trying to (in english) say to excel.. "Look at the worksheet that contains the updated prices, if the part number matches on a certain row, change the price for that row to equal the price given on the changes worksheet."

Is this possible? If so can someone give me some guidance. Thanks!!!

And just for reference.. On worksheet "PriceUpdates506" the part number is Column A and the price is Column C
On the master spreadsheet I am trying to update, the partnumber is Column A and the price is Column F

Originally Posted by MattOneDime

Hello..

I have been trying to figure out how to do this..

I have a master spreadsheet that contains many part numbers, description, weight, price, ect...

Every month I receive an excel sheet that show what prices have changed, and only those that have changed.

Is there a way I can have excel change only the rows that match up to the part numbers that change?

Trying to (in english) say to excel.. "Look at the worksheet that contains the updated prices, if the part number matches on a certain row, change the price for that row to equal the price given on the changes worksheet."

Is this possible? If so can someone give me some guidance. Thanks!!!

And just for reference.. On worksheet "PriceUpdates506" the part number is Column A and the price is Column C
On the master spreadsheet I am trying to update, the partnumber is Column A and the price is Column F

Hi,

One way, insert a new column G, and in G1 put

=IF(ISERROR(VLOOKUP(A1,PriceUpdates506!A:C,3,FALSE)),F1,VLOOKUP(A1,PriceUpdates506!A:C,3,FALSE))

and formula fill that down your sheet.

Copy column G and Paste Special = Values over column F, then delete the inserted column G

Let me know how you go

---

Originally Posted by MattOneDime

Awesome.. with a little tweaking I got that working!! Thank you very much!

a little tweaking?

Hopefully the only change was that the (Forum convention) A1 reference was to the row on which you inserted the formula, to start at row 3 use A3 etc. Because G was an inserted column G1 was vacant, the only twek required was to copy the header from F1 after filling the formula down.

If it needed anything more than that please let me know, but, good to see that it worked for you, and thanks for the response.
---

Originally Posted by MattOneDime

No tweaking to the forumula.. sorry didnt mean it like that.. Just needed to change the starting row since I had the first row as colum titles.. Then I just needed to reduce the calculated amount by 22%.. I also didnt realize that it looked to column F to retrieve the value if no change was detected, so in column F I had all values being reduced by 22% and it was again reducing them by another 22% on items that had not changed.. By removing the calculation at the end of the forumula for col. F I was good to go! Again I really appreciate your help!!

=IF(ISERROR(VLOOKUP(A2,Sheet3!A:C,3,FALSE )),F2,VLOOKUP(A2,Sheet3!A:C,3,FALSE))*0.78

That's very acceptable 'tweaking'


=IF(ISERROR(VLOOKUP(A2,Sheet3!A:C,3,FALSE )),F2,VLOOKUP(A2,Sheet3!A:C,3,FALSE))*0.78

to reduce both,

=IF(ISERROR(VLOOKUP(A2,Sheet3!A:C,3,FALSE )),F2,VLOOKUP(A2,Sheet3!A:C,3,FALSE)*0.78)

should reduce the incoming only, however, good to see that you resolved.
---