Interesting Use of Combo-Box Form Control

Excel 2007 Excel 2003 Macros Excel 2007 Macros Excel 2003 Excel 2010

I work in the HR department of a company. I would like to have a dropdown with many choices in cell A1. Let's say I have about 10 different leadership traits as choices in cell A1. Once a choice in cell A1 is selected I want the cell B1 to be automatically populated with the relevant trait description. I have heard that this can this be done in MS Excel. I have tried the data validation route but it doesn't seem to work. Also I wouldn't like to work with Excel VBA at present. Would appreciate quick help.

The complete process is described below:
  • Write Trait and Description as headers is sheet Traits in cells A1 and B1.
  • In cells A2:A11 enter the traits.
  • In cells B2 to B11 enter the descriptions
  • Select the complete range of cells from A1 to B11
  • Click on 'Formulas'
  • Under the group 'Defined Names' select create from selection
  • From the pop-up window keep only the check box against 'Top Row'. Remove any other selected check box.
  • Click ok
  • Go to sheet Display
  • Click on the developer tab
  • Under the controls group click on insert
  • Select combobox from rhe form controls
  • Click and drag a suitable size of the combobox on your sheet
  • Right click on the control
  • Select Format Control...
  • In the new pop-up window select 'Control'
  • in the Input range text-box type 'Trait'
  • In 'Cell Link' text-box type Traits!C2
  • In the text-box against the 'drop down line' you can type 10 if you wish to show all the traits
  • Now in the sheet Display in cell B2 or any appropriate cell enter the formula '=INDEX(Description,Traits!C2)'
  • This formula now uses the index in cell C2 to get the correct data from the 'Description' named range.

Watch the Excel training video below about using a combo-box form control to display data automatically:

We have described the use of the combo-box also in our earlier video.
Excel 2007 Excel 2003 Macros Excel 2007 Macros Excel 2003 Excel 2010