+ Reply to Thread
Results 1 to 6 of 6

Return Value When Option Selected [SOLVED]

Hybrid View

  1. #1
    Registered User
    Join Date
    02-03-2012
    Location
    Chicago
    MS-Off Ver
    Excel 2010/2011
    Posts
    3

    Return Value When Option Selected [SOLVED]

    I will try my best to explain what I am trying to do. It doesn't seem that complicated in my mind, but I have spent the better part of two days trying to figure this out while searching the internet for an answer.

    On my main worksheet, in column A, is a drop-down list of customer names. This list is populated from a master customer list in a column on a different sheet (we'll call it the 'source sheet') in the same workbook. On that source sheet, in the column next to my customer master list, are corresponding and unique customer numbers.

    I am looking for a formula that will return the customer number in column B on my main worksheet, dependent on what customer is chosen from the drop-down list in column A. Basically, I need the customer names to be "linked" to the customer numbers.

    I know the basic concept can be accomplished with IF statements, but I have hundreds of customers.

    Thank you in advance for any help provided.
    Last edited by Brenzo; 02-03-2012 at 10:59 AM.

  2. #2
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Return Value When Option Selected

    A vlookup will help you. Use the value from the dropdown to search the entire dataset.
    If I have helped, Don't forget to add to my reputation (click on the star below the post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
    Use code tags when posting your VBA code: [code] Your code here [/code]

  3. #3
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Return Value When Option Selected

    Hi

    You can do it, using INDEX&MATCH or VLOOKUP functions.
    Regards

    Fotis.

    -This is my Greek whisper to Europe.

    --Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.

    Advanced Excel Techniques: http://excelxor.com/

    --KISS(Keep it simple Stupid)

    --Bring them back.

    ---See about Acropolis of Athens.

    --Visit Greece.

  4. #4
    Registered User
    Join Date
    02-03-2012
    Location
    Chicago
    MS-Off Ver
    Excel 2010/2011
    Posts
    3

    Re: Return Value When Option Selected

    That's what I figured, but in reading up on both of those techniques, I kept stumbling. It seems like the VLookup formula required you to have a specific criteria in mind (a particular number of text string.)

    How would I design my VLookup, with row 1 on both my main sheet and the source sheet containing headers.

    Main Sheet:
    Column A: Drop-down customer list
    Column B: Where I want to return customer number

    Source Sheet:
    Column A: Master customer list populating the drop-down list.
    Column B: Customer Number

  5. #5
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Return Value When Option Selected

    In the main sheet, cell B2, put this formula -
     =vlookup(A1, Source Sheet!A:B,2,0)

  6. #6
    Registered User
    Join Date
    02-03-2012
    Location
    Chicago
    MS-Off Ver
    Excel 2010/2011
    Posts
    3

    Re: Return Value When Option Selected

    Quote Originally Posted by arlu1201 View Post
    In the main sheet, cell B2, put this formula -
     =vlookup(A1, Source Sheet!A:B,2,0)
    Awesome. Thank you. I was entering 'B' instead of '2' for the column index and left the range lookup blank.

+ 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