Purpose of the guide
Instructs the user how to create choice lists within a worksheet in Excel
Prerequisites
In order to apply this setting, you need to:
- That the workbook is NOT protected;
- That the workbook is NOT shared.
Create a dynamic drop down list in Excel
Dynamic drop-down lists allow you to select a specific item from a list that refers to a range of data.
The dynamism applied allows you to add additional available data to the list without displaying empty lines in it.
- In a new worksheet, type the items to be displayed in the drop-down list
- On the Formulas tab, click Name Management
- Click New to create a new name
- In the Name field enter the name of the desired list, in the Scope field make sure that there is the default option Workbook and in the Refers to field, complete with the following formula:
= OFFSET (Places! $ A $ 2; 0; 0; COUNT (Places! $ A: $ A); 1)
Substitute the following parameters:
Places with the name of the worksheet where the data exists
$ A $ 2 with the correct column and row referring to the first data in the list
$ A: $ A with the correct column in which the list data is present
- Go to the cell in which you want to make the list appear, regardless of whether it is on the same sheet or on a different sheet. Access the Data tab and click Validate data
- In the Allow field select List and in the Source field enter:
= List of Places
Replace SiteList with the name provided above, then click OK
- You can now expand the drop-down list. If you want to add further data to the list, just add it to the bottom of the initially created data list: the dynamic list will update itself and show the new data in the list.