Results 1 to 4 of 4
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Montreal, Quebec, Canada
    Posts
    261
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Two column in-cell dropdown? (2000)

    The short version: How can I use the data validation option to enable the user to select a name from a dropdown list, but have Excel display a letter code?

    The long version: I'm putting together a rather complex expense claim report. We have folks travelling to other countries now and it's going to be a pain to deal with all the different currencies. To make it easy on the travellers and on the book keepers, I'm trying to work up a good system to keep everything straight. I want the traveller to select the region where they were travelling from a dropdown list (simple enough) but have Excel display the internet 2-letter code. The next cell over will then lookup the currency and exchange in order to calculate the value. I want to use the 2-letter code instead of the whole name in order to get the whole thing on one sheet.

    Your thoughts and suggestions, please!
    Louis

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Two column in-cell dropdown? (2000)

    I don't think you can do it with data validation.
    You can use a combobox from forms toolbar. This will put a index number of the list. You can use this to lookup the 2 letter code or to lookup other things

    You could also use the control toolbox combobox and have the a multicolumn list with the 2 letter code column hidden, but as the bound column, so that the selection of the long name will input the short name in the column.

    In both these cases, to avoid have to create multiple objects, it might be easier to create a userform to input the data or possibly use the selection change event linked to the column to create an "object on the fly" for entry and then delete it immediately after the data is entered, though I don't know how practical this might be.

    Steve

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Two column in-cell dropdown? (2000)

    Create a table with country names and abbreviations in a separate worksheet, which can be hidden. You can use the combo box from the Forms toolbar or from the Control Toolbox.

    In the attached workbook, the extra worksheet is not hidden for illustration purposes, and the combo boxes have been placed next to the cells they are linked to. You could move them over the linked cells if desired.

  4. #4
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Montreal, Quebec, Canada
    Posts
    261
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Thanks!

    Very slick! Thanks for putting that together. It might look a bit ugly on a long form, but function is what it's all about!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •