Situatie
A drop-down list is an excellent way to give the user an option to select from a pre-defined list. It can be used while getting a user to fill a form, or while creating interactive Excel dashboards. Drop-down lists are quite common on websites/apps and are very intuitive for the user.
Solutie
Pasi de urmat
- Select a cell where you want to create the drop down list.
- Go to Data –> Data Tools –> Data Validation.
- In the Data Validation dialogue box, within the Settings tab, select List as the Validation criteria.
- As soon as you select List, the source field appears.
In the source field, enter =$A$2:$A$6, or simply click in the Source field and select the cells using the mouse and click OK. This will insert a drop down list in cell C2.
- Make sure that the In-cell dropdown option is checked (which is checked by default). If this option in unchecked, the cell does not show a drop down, however, you can manually enter the values in the list.
Creating a Dynamic Drop Down List in Excel (Using OFFSET)
The above technique of using a formula to create a drop down list can be extended to create a dynamic drop down list as well. If you use the OFFSET function, as shown above, even if you add more items to the list, the drop down would not update automatically. You will have to manually update it each time you change the list.
- Select a cell where you want to create the drop down list (cell C2 in this example).
- Go to Data –> Data Tools –> Data Validation.
- In the Data Validation dialogue box, within the Settings tab, select List as the Validation criteria. As soon as you select List, the source field appears.
- In the source field, enter the following formula: =OFFSET($A$2,0,0,COUNTIF($A$2:$A$100,”<>”))
- Make sure that the In-cell drop down option is checked.
- Click OK.
In this formula, I have replaced the argument 5 with COUNTIF($A$2:$A$100,”<>”).
The COUNTIF function counts the non-blank cells in the range A2:A100. Hence, the OFFSET function adjusts itself to include all the non-blank cells.
- For this to work, there must NOT be any blank cells in between the cells that are filled.
- If you want to create a drop-down list in multiple cells at one go, select all the cells where you want to create it and then follow the above steps. Make sure that the cell references are absolute (such as $A$2) and not relative (such as A2, or A$2, or $A2).
Leave A Comment?