Product lookup and auto-replace cell with new value

[jamief]

Hey Everyone, I am putting together a fairly large workbook and need a bit of help with one aspect of it. It's an inventory tracking and ordering system, and it has a couple different pages for different features. One page is a master price list to be updated monthly with new prices etc.

I am looking to add a button that I could push, which look at every Sku# in a certain row, and find the corresponding entry on another page. They will be organized differently, so it will most likely need a lookup feature. There are several hundred products in the form, so it could maybe have a row range that it would look up each cell on the other page, then update the price from a couple cells over.

I have made a dummy version of it to upload on here. I appreciate any and all help, as i'm fairly new to excel. Thanks!!

-Jamie

sample.xlsx

[etaf]

Will the updated Master price list contain ALL the Sku numbers with a price - of which some maybe updated

then a lookup would work

only issue would be if the sku is not on the master list - in my lookup suggestion

on sheet 1 cell D4 put
=INDEX(Sheet2!C:C,MATCH(Sheet1!A4,Sheet2!B:B,0))
and copy down

now the price is linked to the price all the time - so any updates will feed through straight away

[xladept]

Hi Jamie,

Do you want the new prices on Sheet1 meaning that Sheet2 is the master or the other way around?

[jamief]

Thanks for replying guys, Etaf; The Master list will contain all Sku's unless they happen to change on occasion. The order of them will change though, if that makes any kind of difference.

And Xladept, Sheet 1 is the useable inventory sheet, and sheet 2 is the master price from the company we order off of.

Thanks Guys!

[etaf]

order should make no difference and if they have them all - then any update will be included immediately
HOW do you make the update - is it just copying new information into the whole of the sheet
if so - then making the master data include a table , so that it will correctly reference, regardless of how many rows

if my assistance has helped, and only if you wish to , there is a reputation icon * on the left hand side - you can add to my reputation here
If you have a solution to your thread - Please mark your thread solved do the following: >
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

[jamief]

Thanks Etaf! This should work perfectly!

[etaf]

your welcome, thanks for the rep

[jamief]

Hey Etaf, Everything worked good in the sample I put up with all the values, but when i transfered it over to the actual workbook, it isn't working. I edited the reference cells to fit my workbook, So it was probably something stupid I did haha.

Ok in cell D14 on the first sheet, the code has been inserted to match all, but it's not finding the other cells and coming up with a #N/A error on it.

Thanks!

LWB LICORS P7W1.xlsm

[jamief]

edited, posted twice

[etaf]

The prices column B is formatted as text and all the numbers are being seen as text numbers
and so do not match

click on the prices column B , so the whole column is selected
than goto
DATA
text to columns
delimited
and just go
next next finish

that will change all the numbers in the column from TEXT to numbers and now the match will work

you may need to do that each time you copy across the new price information

[jamief]

Success! Thanks so much!

[etaf]

your welcome,