200+ Video Lessons
50+ Hours of Video
200+ Excel Guides
Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)
I want to create a drop down list to insert abbreviations into a cell.
The cell should contain the abbreviation, but I want to see in the drop down list an explaination of the abbreviation.
Is this possible with a simple setup or do I need to make two cells and fill in the abbreviation by using lookup or similar functions?
The "simple" way Excel intends to offer consists of a multi-column ComboBox where one column is shown in the drop-down and another used to fill the linked cell. However, when I consider that you will have to store the values for the two columns somewhere the idea you outline would seem to be not more complicated.
It all burns down to a definition of "simple". In the attached workbook I created two overlapping named ranges, Index and Dropdown, where Dropdown is in the first column of Index. This setup supports VLOOKUP. INDEX/MATCH won't require that (columns could be in any sequence). The setup for a ComboBox would look pretty much the same.
I pointed the cell validation in C3 to the Dropdown range and used the different lookups in column A.
=INDEX(Index,MATCH(C3,Dropdown,0),2)and
=VLOOKUP(C3,Index,2,FALSE)