+ Reply to Thread
Results 1 to 12 of 12

Product lookup and auto-replace cell with new value

  1. #1
    Registered User
    Join Date
    02-05-2014
    Location
    Canada
    MS-Off Ver
    Excel 2013
    Posts
    29

    Question Product lookup and auto-replace cell with new value

    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

  2. #2
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 for Mac OSX & Windows
    Posts
    7,326

    Re: Product lookup and auto-replace cell with new value

    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
    Wayne

    if my assistance has helped, and only if you wish to , there is an "* Add Reputation" 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.

  3. #3
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,379

    Re: Product lookup and auto-replace cell with new value

    Hi Jamie,

    Do you want the new prices on Sheet1 meaning that Sheet2 is the master or the other way around?
    If I've helped you, please consider adding to my reputation - just click on the liitle star at the left.

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~(Pride has no aftertaste.)

    You can't do one thing. XLAdept

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~aka Orrin

  4. #4
    Registered User
    Join Date
    02-05-2014
    Location
    Canada
    MS-Off Ver
    Excel 2013
    Posts
    29

    Re: Product lookup and auto-replace cell with new value

    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!

  5. #5
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 for Mac OSX & Windows
    Posts
    7,326

    Re: Product lookup and auto-replace cell with new value

    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.

  6. #6
    Registered User
    Join Date
    02-05-2014
    Location
    Canada
    MS-Off Ver
    Excel 2013
    Posts
    29

    Re: Product lookup and auto-replace cell with new value

    Thanks Etaf! This should work perfectly!

  7. #7
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 for Mac OSX & Windows
    Posts
    7,326

    Re: Product lookup and auto-replace cell with new value

    your welcome, thanks for the rep

  8. #8
    Registered User
    Join Date
    02-05-2014
    Location
    Canada
    MS-Off Ver
    Excel 2013
    Posts
    29

    Re: Product lookup and auto-replace cell with new value

    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

  9. #9
    Registered User
    Join Date
    02-05-2014
    Location
    Canada
    MS-Off Ver
    Excel 2013
    Posts
    29

    Re: Product lookup and auto-replace cell with new value

    edited, posted twice

  10. #10
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 for Mac OSX & Windows
    Posts
    7,326

    Re: Product lookup and auto-replace cell with new value

    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

  11. #11
    Registered User
    Join Date
    02-05-2014
    Location
    Canada
    MS-Off Ver
    Excel 2013
    Posts
    29

    Re: Product lookup and auto-replace cell with new value

    Success! Thanks so much!

  12. #12
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 for Mac OSX & Windows
    Posts
    7,326

    Re: Product lookup and auto-replace cell with new value

    your welcome,

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] lookup that adds up the sum product based on another cell.
    By perky derky in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 12-14-2013, 05:42 AM
  2. LOOKUP() replace with formula in cell instead of value
    By sleepymatt in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 05-09-2013, 04:25 PM
  3. Replies: 6
    Last Post: 06-16-2010, 01:18 PM
  4. Two Dimensional Lookup and Cell replace
    By hard4me in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 09-03-2008, 05:09 PM
  5. Auto-replace typed contents in a cell with a formula
    By Josiah in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 04-10-2008, 02:41 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1