+ Reply to Thread
Results 1 to 4 of 4

Link data in different sheets into master view

  1. #1
    Registered User
    Join Date
    04-10-2019
    Location
    Hong Kong
    MS-Off Ver
    Office365
    Posts
    4

    Link data in different sheets into master view

    Hi all,

    The situation is that I have a master sheet, which is my overview sheet with the columns model, price supplier 1, price supplier 2, etc
    sheet 2 is pricing from supplier 1 (model, price)
    sheet 3 is pricing from supplier 2 (model, price)

    I want to merge data from sheet 2 and 3 into the master sheet
    Supplier data model name can differ per supplier, so my master sheet model name is a combination of all different supplier model names for that product together
    There must be a partial match where the whole of supplier model names matches partially with my model name in the master sheet
    Also the order of products per supplier is different.

    What would be the best method in matching the supplier model name with any row in sheet 1 and fill out the supplier price in the supplier column in the master sheet?

    Thanks for all the help.
    Gijsbert
    Attached Files Attached Files

  2. #2
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2016
    Posts
    4,985

    Re: Link data in different sheets into master view

    Hi, welcome to the forum.
    I have not looked at your file but if you have let's say your own product list in a worksheet and you have suppliers which have the same product but under another name I would do the following.
    reserve one sheet for the master list in column A and leave the next 10 columns empty.
    When you have a supplier with his own description for that same part you add the name next you yours in the next first empty column and so for all suppliers
    So when you receive a list and search on the suppliers 'product name make a search in the area A:K and if found your name is the one in column A of the found row.
    I do this when I allocate column headers to other files which have the same data but use different columns.
    Hope my explanation makes a little bit sense
    Hans
    "IT" Always crosses your path!
    May the (vba) code be with you... if it isn't; start debugging!
    If you like my answer, Click the * below to say thank-you ( It doesn't hurt )

  3. #3
    Registered User
    Join Date
    04-10-2019
    Location
    Hong Kong
    MS-Off Ver
    Office365
    Posts
    4

    Re: Link data in different sheets into master view

    Hi Keebellah,

    I made progress, but I'm stuck right now.
    Current situation, in the master I've got 3 columns with 3 product names for the same product.
    If the supplier product matches with any of the 3 names, I want to execute the Vlookup
    =VLOOKUP($B6,Supplier_1!$A$3:$B$1000,2,)/VLOOKUP($I$2,Rates!$Q$2:$R$50,2,)

    Vlookup is currently only working on field B6
    If I change $B6 to $B6:$D6 it isn't working

    My intension is I want to use ANY of the fields (B6, C6 or D6) to function as Vlookup search key.
    Is that possible?

    Gijsbert

  4. #4
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2016
    Posts
    4,985

    Re: Link data in different sheets into master view

    There is something I don't understand and that's because I'm not the great with formuls.
    What does the / do ?
    Please Login or Register  to view this content.
    And is thsi formula on row 6 in the master under Supplier 1?

    If your looking inside a range I think you should be using INDEX and MATCH and use an ARRAY formula Shift+Ctrl+Enter

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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