How to create a drop down list but show different values in Excel 2016

Question

I created a drop down list, but I want it to function so that when you select from it, it displays something else.  For example: If the dropdown list contained the names of the states, I would want to have the drop down list show the full name, but when you select it, I want the abbreviation to be displayed.  Select New York, but have NY be displayed.  I have created the two lists but I can't figure out how to get the substitution to work.  I'm afraid what seems simple may be very complicated. Thoughts?

< moved from: Office/Excel /Windows other /Office 2016 >

Answer 1

You can do that if you use an ActiveX combo box...

Go to Developer tab. Under Controls...Insert...ActiveX Controls select Combobox. Click where you want it on the sheet. Right click the Combobox, select Properties. In ListFillRange, type the range of your columns with the data to fill the list. Set ColumnCount to 2.

If your first column has New York and second column has NY, set ColumnWidths to 20 pt;0 pt and change BoundColumn to 2. If it's the other way around, adjust accordingly.

Enter the cell to show results in LinkedCell.

Click "Design Mode" button on ribbon to turn it off and it should now work.

Answer 2

Jason may have better experience with ActiveX- after getting burned on a few projects, I quit using ActiveX altogether.

The only thing I can think of would be to include both in your lookup column, formatted like:

AL  Alabama

AK  Alaska

AZ  Arizona

AR  Arkansas

CA  California

Use Data Validation to point to this list (I'm assuming you are doing this on a worksheet and not a userform, but you didn't specify)

Then shrink the column width so that only the first two letters show, but when you click the cell the DV downarrow will still appear, and you will still be able to see the state names.

If you need just the left two characters for some other purpose (vlookup, match/index, etc.) then just wrap the cell reference in a LEFT formula

=LEFT(B1,2) to get the 2-digit ID <- this goes in the cells referencing this one, not the DV cell itself

You may want to add a single spacebar (blank char) in the cell to the right of your DV cell, so the full value doesn't show across unused cells. Also, if the DV box isn't wide enough- in my test case, I couldn't see the last few chars of "California" but I could see enough.

HTH

Answer 3

Jason may have better experience with ActiveX- after getting burned on a few projects, I quit using ActiveX altogether.

The only thing I can think of would be to include both in your lookup column, formatted like:

AL  Alabama

AK  Alaska

AZ  Arizona

AR  Arkansas

CA  California

Use Data Validation to point to this list (I'm assuming you are doing this on a worksheet and not a userform, but you didn't specify)

Then shrink the column width so that only the first two letters show, but when you click the cell the DV downarrow will still appear, and you will still be able to see the state names.

If you need just the left two characters for some other purpose (vlookup, match/index, etc.) then just wrap the cell reference in a LEFT formula

=LEFT(B1,2) to get the 2-digit ID <- this goes in the cells referencing this one, not the DV cell itself

You may want to add a single spacebar (blank char) in the cell to the right of your DV cell, so the full value doesn't show across unused cells. Also, if the DV box isn't wide enough- in my test case, I couldn't see the last few chars of "California" but I could see enough.

HTH

Thank you.  I guess I wasn't clear--first time poster here--sorry.

Yes, I am trying to accomplish this using Data Validation.

I only used the names of states and their abbreviations in my question as a familiar example, but that's not the exact thing I'm working on.  In all of my entries the abbreviation for the detailed name would be more than 2 characters and not all the same length. 

In my searching, before I posted my question, I saw an example where the drop down has peoples' names and when you selected one, their ID number appeared instead of their name.  I'm trying to do something like that.

Answer 4

No need to apologize (although now I feel the need to apologize, if I sounded in any way snarky- that wasn't my intent) and the forum welcomes all new participants who want to learn more and use Excel better :)

I think you could use the same approach, especially if the ID is a fixed width. As long as the value you want to remain visible is on the left, you are fine. The bigger issue will be playing around with column width, because longer names may not show enough info to reliably pick the right value

123456  Robertson, Jaime

234567  Robertson, James

If you can't see the last few characters, you can't be sure you have the right one.

Alternatively, if you are willing to have a slightly less perfect interface, you could use two different cells, where one cell is the DV lookup, and the other then takes the selected value and returns the ID. Depending on your usage, this isn't so bad- you can bring back more than just the ID if you want (tenure, last performance rating, boss, company email address- whatever you have in your table)

However, if you absolutely need to hide the selection, I suspect you are stuck with ActiveX (per Jason's idea), or a VBA solution which will require all your end users to enable macros. Not a big deal if you are the only user (or work closely with users), but definitely a challenge if this workbook is going to be shared with folks who may not want to enable macros.

Answer 5

Laurie,

re: alternate display of selection

Another way...

  '---

  Jim Cone

  Portland, Oregon USA

  https://goo.gl/IUQUN2   (Dropbox)

  (free & commercial excel add-ins & workbooks)