When I was preparing a sheet by referencing some pre-defined names as list to a field/cell. I was thinking the feature of Microsoft Excel dropdown list. After exploring the option and using, I thought of sharing with you all.
Objective: Selecting a cell value from a list of pre-defined values
Procedure:
- Open blank excel workbook
- Prepare you template with headers
- Create a new sheet and rename it. Ex: "Names"
- Enter the values from Column A from cell A1 in vertical direction
- Don't use any other columns
- Don't leave any empty cell in between the values
- Leave first cell as empty, if you want to include the empty option in a dropdown
- Select the cell which contains the values
- Right click and choose the option "Define Name"
- Give a name to the list of values. Ex: NamesList
- Click on "OK" button
- Now, move to the sheet where we want to use the list of names as a dropdown option
- Select the cell(s), where we want to have the dropdown list
- Click on "Data" Menu
- From "Data Tools" ribbon, click "Data Validation" from "Data Validation" submenu
- In Settings tab, Validation Criteria section, for the option Allow, select "List"
- Choose the options
- Ignore blank: Ignore when user no selected any option
- In-cell Dropdown: Show the dropdown list in cell
- In "Source" field, type the defined name for the list preceded with = (Equal to) symbol. Ex: =NamesList
- Now, you should be able to see the dropdown with empty values
- Select the predefined values from the dropdown list of names.
