A combo box combines a text box with a list box to create a drop-down list from which you can pick a value.
In this tutorial, we will look at how to get the selected value in a combo box and how to get all the values in the input range to which the combo box is linked.
How to Get the Selected Value in a Combo Box
Suppose we have the following Select a Category combo box whose input range is A2:A9 and whose cell link is cell E1. The cell link displays the row number of the selected value in the input range.
We want any selected value in the combo box to be displayed in cell E2.
We use the INDEX function to display in cell E2 the value selected in the combo box. The INDEX function will return the value in the combo box that is in the row displayed in cell link E1.
We use the following steps:
- Select cell E2 and type in the following formula:
|
1 |
=INDEX(A2:A9,E1) |
- Press Enter or click the Enter button on the formula bar.
The Garden, Tools & Hardware category selected in the combo box is displayed in cell E2 and you can refer to it anywhere else in the workbook.
How to Get All the Values of a Combo Box
Suppose we have the following Select a Category combo box whose input range is A2:A9 and whose cell link is cell E1. The cell link displays the row number of the selected value in the input range.
We want to get all the values in the input range of the combo box and display them in the range beginning at cell E2.
We use the following steps:
- Select cell E2 and type in the following formula:
|
1 |
=INDEX(A2:A9,0) |
- Press Enter or click the Enter button on the formula bar.
All the values in the input range of the combo box are displayed in range E2:F9.
Conclusion
In this tutorial, we looked at one technique for getting the selected value in a combo box using the INDEX function. We also looked at another technique for returning all the values in the input range of a combo box using the INDEX function.