Interesting Use of Combo-Box Form Control

I work in the HR department of a company. I would like to have a drop-down 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 the 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:

One thought on “Interesting Use of Combo-Box Form Control

  1. jarnail

    How we can use index function with combo box(active x control) to pull data from multiple columns in excel 2010


Leave a Reply

Your email address will not be published. Required fields are marked *