Pulling information from other worksheets in a workbook

  • IF there is a customer # in cell D2 on the main worksheet I need it to pull the customer's name from the Customer Base Price Sheet (worksheet within the same workbook) the customer's name is located in column B next to the customer's # on the customer base price sheet worksheet.
    I am needing it to automatically give my worksheet the customer's name whenever I enter in the customer's number.

  • Re: Pulling information from other worksheets in a workbook


    Hi Alayna and welcome,


    I couldn't decide whether you just wanted the customers name returned in a cell next to the customer # or whether you actually wanted to change the sheets name.
    So I did both :)


    Quote

    I am needing it to automatically give my worksheet the customer's name


    To do a lookup in a cell use

    Code
    E2: =VLOOKUP(D2,'Customer Base Price'!A2:B11,2)


    In order to change the sheetname you will need to use some VBA code.
    Add this to the code module of the sheet you want to change.



    If you are changing sheet names you will have to careful that the customer names do not contain any illegal characters.

    [h4]Cheers
    Andy
    [/h4]

  • Re: Pulling information from other worksheets in a workbook


    I entered the formula: =VLOOKUP(D2, 'CUST BASE PRICE SHEET'!A2:B11,2) and it pulls generally what I need however its not pulling the names quite right... My list for the customer number/name is layed out like the following:


    A2 B2 C2
    10001 New Customer #1
    41276 Bargains #3


    This list goes from cells A2;A40 B2;B40 and C2;C40


    On some of the customer #s listed in column A (A3 for example) instead of pulling the name from B3 it is pulling a name from B10


    How can I fix that?

  • Re: Pulling information from other worksheets in a workbook


    The quickest way would be to change the layout of your table ;)


    A2 B2 C2
    #1 10001 New Customer
    #3 41276 Bargains


    Code
    =VLOOKUP(D2, 'CUST BASE PRICE SHEET'!A2:B11,3)


    If you can not change the layout of the table then use this revised formula.

    Code
    =OFFSET('CUST BASE PRICE SHEET'!C2,MATCH( D2, 'CUST BASE PRICE SHEET'!C2:C11,0)-1,-1,1,1)

    [h4]Cheers
    Andy
    [/h4]

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!