Excel has a very powerful feature providing for a dropdown select list in a cell, reflecting data from a named region. It'a a very easy configuration, once you have done it before. Two steps are to follow:
- Create a named region,
- Setup the dropdown in a cell.
To create a named region (rather on another sheet) just select some consecutive cells in a column and enter a region name in the 'Name Box' above cell A1. Hit the Enter key.
An alternative method would be to right-click on the selected area of selected cells and apply the 'Name a Range...' command from the context menu. Leave scope be 'Workbook' as suggested.
Test you work so far clicking on a cell outside of the area to really leave the area, than select same cells again - The name of the area should appear in the 'Name Box'.
Now we go to next step, the dropdown setup. Select a cell in you main sheet, then click 'Data' menu item on the top, then click 'Data Validation' on the right. On the Data Validation popup screen > Settings > Validation criteria > Allow select 'List' from the dropdown and enter in the 'Source' text box above region name preceded by an equality sign.
Hit OK and you will see the dropdown arrow next to you cell, which will appear each time the cell is selected and show the dropdown list items when clicked.
Basically, that's all. You can go further, refining you dropdown style, with opening 'Data Validation' again and playing with the options under 'Error Alert' allowing you to allow with/without warning or fully disable entering an item which is not in the list of items.
Enjoy Excel!
