Let us say you have a cell in a Microsoft Excel worksheet in which you want to allow the user to enter a location, but you want the user to select the location from a specified list of locations that you provide (such as San Francisco, Boston, and New York). How can you accomplish this?
First of all, you enter the locations (San Francisco, Boston, and New York in this case) in an Excel worksheet (preferably a different worksheet from the one where the user will be entering data) and name the range of cells accordingly. Here are the steps involved:
2. Bring the cursor to the white box to the left of the formula bar (called the “name box”) and type the appropriate name that represents your range (valid_locations is the name we chose in this case).
3. Press ENTER to accept the name.
Second, go back to the Sheet1 where you want the user to enter the location, and select the desired cell. Then follow the following steps:
2. When the Data Validation window opens up, click on the “Allow” drop down list and select “List” and then type “=valid_locations” in the “Source” field (as indicated in the picture on the left).
3. Press Okay and your cell gets transformed into a drop down list from which the user can select one of the valid locations you specified.