+ Reply to Thread
Results 1 to 7 of 7

vlookup to find a product based on code, but still able to find product when code changes

  1. #1
    Registered User
    Join Date
    10-06-2014
    Location
    birmingham
    MS-Off Ver
    2013
    Posts
    7

    vlookup to find a product based on code, but still able to find product when code changes

    hi all

    I know that this problem may have no solution without some very very advanced excel(ing)

    basically, I have created a costing workbook for a kitchen which can cost 250 a la carte dishes, wedding packages, conferences, set menus etc. It works by putting each supplier into separate sheets so when a new price list is issued, it can be copied and pasted in. Then there is a mastersheet which gets all of its information from every supplier so all of the information require for the workbook comes from here. When costing a new dish, type in the product code and all of that products information automatically goes into the page.
    However, the problem I have is when the supplier changes the products code.
    is there any way of if the product code changes, then it can also change the product code everywhere that it appears in the work book
    (for example, if lettuce code is 1001, and they change it to 1002, everywhere that used product code 1001 within the entire workbook will automatically change to 1002)

    I have searched hard and tried to think for myself if anyway possible and I cant seem to find a solution.
    If anyone knows anyway possible (if it is possible) then I am truly grateful as you will have solved the biggest fault with this workbook
    thanks

  2. #2
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often: Office 365 in Windows environment
    Posts
    8,609

    Re: vlookup to find a product based on code, but still able to find product when code chan

    In the suppliers sheet you probably have columns with: Product name, product code and product price.
    In other pages, where you "use" the product, such as a given dish page, do not write just the code, but refer to cell with code, just like:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    This way you can change the code just in the suppliers sheet.

    Another way (if codes are unique) - you can do find-and-replace (Ctrl+H) in whole workbook (set this in search options, not to limit scope just to current sheet - the default setting)
    Best Regards,

    Kaper

  3. #3
    Valued Forum Contributor
    Join Date
    05-14-2012
    Location
    Unknown
    MS-Off Ver
    Unseen
    Posts
    429

    Re: vlookup to find a product based on code, but still able to find product when code chan

    I think the only way to achieve it is using VBA. You could use a Selection Change Event handler to store the current value of a cell, for example, the product code. Then, if the product code is changed, you could do a global replace on each sheet to replace the stored value with the new value.

    Easy enough in principle . That said, you'd need to be careful to monitor only cells/columns with product codes and replace whole cell values, not partial matches.

    At the moment, downloading and uploading sample workbooks is a pain given the malware on the site. My AV software starts running interference as soon as I open the EF page. However, for anyone to provide effective help, they will need to see a sample workbook.

  4. #4
    Registered User
    Join Date
    10-06-2014
    Location
    birmingham
    MS-Off Ver
    2013
    Posts
    7

    Re: vlookup to find a product based on code, but still able to find product when code chan

    hi kaper

    it is absolutely amazing how one solution provides another problem. I have played around with the ctrl+h and replace all in work book which works fantastically to change items product codes. This has helped grately. However, as you said with the "unique" codes, with 5000+ items on our supplier lists, there is a couple of product codes (such as beef mince code 150) which then creates the issue. when using 150g of an item, if I change 150 to the new code, then this also changes the 150 for the weight used (if this makes any sense)
    any further suggestions?

  5. #5
    Registered User
    Join Date
    10-06-2014
    Location
    birmingham
    MS-Off Ver
    2013
    Posts
    7

    Re: vlookup to find a product based on code, but still able to find product when code chan

    hi
    thanks invisible man. your post seems to offer a glimmer of hope that what I want is achievable. However, being just a chef, and most of what I have learnt on excel is sel taught, I have never managed to get into such debth as VBAs or macros or anything like this. Is there anywhere that may be very good for learning these things?
    thanks

  6. #6
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often: Office 365 in Windows environment
    Posts
    8,609

    Re: vlookup to find a product based on code, but still able to find product when code chan

    Hi,

    I believe that changes in code are probably rather occasional, than routine. Two propositions in such case:
    1) use Crtl+H but not with replace_all, but replacing one-by-one - especially if the code looks as "potentially not only a code" : 100 (grams), 43 (Spanish licor Cuarenta Y Tres) etc.
    or 2) do large work once and then have easy life: add prefix to codes in spreadsheet like: SC_150 (supplier code 150) instead of just 150. Once this large work is done, replacing some day SC_150 by SC_151 will be pretty easy and quick.

  7. #7
    Valued Forum Contributor
    Join Date
    05-14-2012
    Location
    Unknown
    MS-Off Ver
    Unseen
    Posts
    429

    Re: vlookup to find a product based on code, but still able to find product when code chan

    Using Kaper's approach, select the column(s) with product codes before doing your Replace All.

+ 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. Check a value based on product code and date
    By DirectGardenBuilding in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 10-27-2013, 05:33 AM
  2. Link product image to product code
    By rogan in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 07-10-2013, 02:51 AM
  3. [SOLVED] 4 digit code for similar product, new product unique code
    By unclejemima in forum Excel General
    Replies: 47
    Last Post: 06-06-2013, 01:02 PM
  4. Find and go to product code in a list of products
    By ALANBERNARD in forum Excel General
    Replies: 5
    Last Post: 06-05-2013, 09:28 AM
  5. [SOLVED] Trying to use VLookup with drop-down menu to get product code and price
    By rtee in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 11-18-2012, 07:02 PM
  6. [SOLVED] Find Product Details in Another Sheet based on Product ID and Copy some Fields From there
    By kevalkothari in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 08-29-2012, 10:43 AM
  7. [SOLVED] Need Function that will find ordered product, and display the product code in a 2nd workbk
    By rollerden in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-21-2012, 08:52 AM
  8. Value lookup based on product code and quantity
    By alepoutre in forum Excel General
    Replies: 3
    Last Post: 08-15-2011, 10:45 AM

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