+ Reply to Thread
Results 1 to 18 of 18

Displaying and Returning different value in Dropdown List

  1. #1
    Registered User
    Join Date
    04-22-2010
    Location
    London
    MS-Off Ver
    Excel 2003
    Posts
    7

    Displaying and Returning different value in Dropdown List

    Helo, Can anybody help me with Drop down list.
    I know how to create drop down list but I have a problem to Return different value then is displayed in Dropdown List. For example:
    On the workbook I have two wokssheets: one with empty cell and secound with list created for dropdown list.

    On the secound woksheet I have created: column A namebox "Country" - cointains full name of countries (Great Britain, Australia ...) and column B namebox "CountryCode" - contains country codes only (GBR, AUS ...)
    When I click on dorp down list on first worksheet, I would like to my drop down list display me list with columnA "Country" but when I choose one of that it would returns me cell from columnB "Country Code".
    Is there any easy way to do that?

  2. #2
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Displaying and Returning different value in Dropdown List

    Kind of confusing... but I think in the end you're looking for Vlookup

    e.g. =Vlookup(A1,'Sheet 2'!A:B,2,FALSE)

    where A1 is the dropdown menu cell and Sheet 2 is name of the sheet with the table of values.
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

  3. #3
    Forum Contributor wamp's Avatar
    Join Date
    11-10-2008
    Location
    Norway
    MS-Off Ver
    97, 2000 & 2003 (diff. workstations)
    Posts
    184

    Re: Displaying and Returning different value in Dropdown List

    I believe NBVC has your solution.

    as an alternative, you could also see this forum thread .
    That refers to a similar question.

  4. #4
    Registered User
    Join Date
    04-22-2010
    Location
    London
    MS-Off Ver
    Excel 2003
    Posts
    7

    Re: Displaying and Returning different value in Dropdown List

    Thanks NBVC,
    This unfortunatelly doesn't wok because error displayed " You may not use references to other worksheets or workbooks for Data Validation criteria"

    I belevie this need to be input in Data Validatios setings source box.

    Any further suggestions please?

  5. #5
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Displaying and Returning different value in Dropdown List

    The formula goes in an empty cell (not where the data validation cell is)..

    So when you select a country, the code will appear in another cell (where the formula is). It does not replace the dropdown list.

  6. #6
    Registered User
    Join Date
    04-22-2010
    Location
    London
    MS-Off Ver
    Excel 2003
    Posts
    7

    Re: Displaying and Returning different value in Dropdown List

    I have been trying to follow your instructions NBVC but I didn't managed to set this up. I am bit lost now. To make this clear for you WAMP linked me to similar problem which was:

    "I want to create an Excel Drop-Down menu with the below condition:
    - I have 4 items in columns A and B, respectively, as listed below:
    BVLV | Ball Valve
    BTLV | Butterfly Valve
    GVLV | Gate Valve
    GBLV | Globe Valve
    - The drop-down menu displays column B, however, upon selecting any item from this column, only the four-letter "valve code" from column A is returned."

    This is nearly exactely what I want. The my situation is I have drop down list on first worksheet and my list on second worksheet.

    When I have been trying to paste your instruction to cell the window came up asking for destination file and after directing to file/sheet 2 I ended up with error on the cell.

  7. #7
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Displaying and Returning different value in Dropdown List

    After you paste the formula, you will have to make adjustments to it.

    You need to change A1 to the actual cell reference your dropdown cell is at.

    You need to change 'Sheet 2' to the name of the sheet your table is on.

    You need to change A:B to the column range than your table is actually in within that other sheet... and if they are not next to each other... then you may need to change the 2 to the actual column number within that range you want to extract from.

    Have a look at VLOOKUP in Excel help for direction and more samples on how it works.

    If you really have difficulty, post your sample workbook (no confidential info in it please)

  8. #8
    Registered User
    Join Date
    04-22-2010
    Location
    London
    MS-Off Ver
    Excel 2003
    Posts
    7

    Re: Displaying and Returning different value in Dropdown List

    ok this is my example:

    worksheet 1 named "Sheet1" - drop down list in cell A2 (drop down list has been created as data/validation/settings/list/source-Country). Country is the namebox, it means A1:A10 in worksheet2.

    worksheet 2 named "Sheet2":

    ---A-----------------------------------------------------B
    1 GBR GREAT BRITAIN Citizen------------GBR
    2 ABW ARUBA------------------------------------ABW
    3 AFG AFGHANSIATAN-----------------------AFG
    4 AGO ANGOLA----------------------------------AGO
    5 AIA ANGUILLA----------------------------------AIA
    6 ALA ALAND ISLANDS-----------------------ALA
    7 ALB ALBANIA -----------------------------------ALB
    8 AND ANDORRA--------------------------------AND
    9 ANT NETHERLAND ANTILLES -----------ANT
    10 ARE UNITED ARAB EMIRATES--------ARE

    What I need it is when I pick up from dropdown list (on worksheet 1) country it should return country code.
    Last edited by Mirek; 04-22-2010 at 11:59 AM.

  9. #9
    Registered User
    Join Date
    04-22-2010
    Location
    London
    MS-Off Ver
    Excel 2003
    Posts
    7

    Re: Displaying and Returning different value in Dropdown List

    I am afraid this is impossible without macro involved.

  10. #10
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Displaying and Returning different value in Dropdown List

    =Vlookup(A2,Sheet2!$A$1:$B$10,FALSE)

    Put this formula in cell B2 of Sheet1 (beside the dropdown list cell).

  11. #11
    Registered User
    Join Date
    04-22-2010
    Location
    London
    MS-Off Ver
    Excel 2003
    Posts
    7

    Re: Displaying and Returning different value in Dropdown List

    Thanks NBVC but this showing me value error. This is I think because you perhaps misted col-index-num.
    Even it would work I could not be puting next to drop down cell because there are following columns used for deferent data.

    I found some forum with similar problem listed but this still involves a marco:

    "I was just wondering if there is a way to do the following:

    Have created a drop down menu using data/validation. This menu is a
    list of project names. What I would like is, when name is selected from
    this menu, only the first letter of this name actually appears in the
    cell. So if the choose, France, F should appear. Its important that the
    whole words are available in the drop down.
    Any ideas anyone?"

    link to this forum:
    http://www.keyongtech.com/1324143-wh...s-in-drop-down


    If this would be resolved without using macro that could be good enough for me.
    Last edited by Mirek; 04-23-2010 at 12:16 PM.

  12. #12
    Registered User
    Join Date
    04-22-2010
    Location
    London
    MS-Off Ver
    Excel 2003
    Posts
    7

    Re: Displaying and Returning different value in Dropdown List

    If anybody knows how return limited leters in drop down validation list??!! HELP!

  13. #13
    Registered User
    Join Date
    09-17-2013
    Location
    Charlotte, NC
    MS-Off Ver
    Excel 2007
    Posts
    1

    Re: Displaying and Returning different value in Dropdown List

    Hello Everyone,
    I know I am about three years late to this party, but I figured I would comment anyway. This is my first post and I am by far not an expert with VBA, but I was trying to accomplish the same thing as Mirek. I needed to have the dropdown list show the user one thing, but when they select that item from the drop down, a different value gets stored in the target cell.
    I don't think the original request for this was ever solved so I messed around with it (using VBA) and I think I got it to work. I am sure my code needs some cleanup and that there may be a better way to get it done, but I figured I would share anyway.

    My code basically stores the "state name" value selected in the dropdown, looks it up in a data table and returns the "State Abbrv" to the target cell, then stores the result as a value.

    I would appreciate feedback on where the code could be made better as I am self taught with VBA and always looking to learn a better way of doing things.

    See attached file for an example

    Private Sub Worksheet_Change(ByVal Target As Range)
    On Error GoTo Error_Handler

    'Return Abbrv after selecting Full State Name
    If Not Intersect(Target, Range("f5:f10")) Is Nothing Then
    Application.EnableEvents = True
    ScreenUpdating = False
    Dim myval As String
    myval = Target.Value
    If Target.Value = "" Then
    Exit Sub
    Else
    End If
    Application.EnableEvents = False

    Target.Value = _
    "=IFERROR(VLOOKUP(""" & myval & """,'Data Tables'!$a$2:$c$53,2,FALSE),""" & myval & """)"

    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    Application.CutCopyMode = False
    Application.EnableEvents = True
    ScreenUpdating = True

    Else

    End If

    Error_Handler:
    Application.EnableEvents = True
    Exit Sub
    End Sub
    Attached Files Attached Files

  14. #14
    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: Displaying and Returning different value in Dropdown List

    jshawclt

    Welcome to the forum.

    Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

    If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

    Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.

    Aslo

    Remember to your new thread to add code tags around your code as per forum rule#3
    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.

  15. #15
    Registered User
    Join Date
    01-16-2014
    Location
    Southern California
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: Displaying and Returning different value in Dropdown List

    I appreciate him posting this 3 years later even if it wasn't his post. So Im confused how someone is supposed to answer a post. Are they just using the wrong thing to answer it?

  16. #16
    Registered User
    Join Date
    01-16-2014
    Location
    Southern California
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: Displaying and Returning different value in Dropdown List

    I like this and it works great, only problem I am having is I don't want my list to be in the same sheet, I'm trying to figure out a way to have my list in another sheet to reference, when I reference another sheet, it tries to open up a file??

  17. #17
    Registered User
    Join Date
    01-16-2014
    Location
    Southern California
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: Displaying and Returning different value in Dropdown List

    Now I just want to use this vba over and over, but it won't let me, I have to combine. Pain, because I can't figure out the syntax to do it. This is the only one I have found that does this? I have not found any other solutions.. If you have more ideas, let me know. THanks.

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

    Re: Displaying and Returning different value in Dropdown List

    miztify,

    Unfortunately you need to post your question in a new thread, it's against the forum rules to post a question in the thread of another user. If you create your own thread, any advice will be tailored to your situation so you should include a description of what you've done and are trying to do. Also, if you feel that this thread is particularly relevant to what you are trying to do, you can surely include a link to it in your new thread.
    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]

+ 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