There are some occasions when you would like to limit the possible values that can be entered into in a Google Sheets spreadsheet cell to a list of specific, pre-defined values.
Those values can themselves be conveniently stored in another range on the same, or a different sheet.
Suppose we have this spreadsheet, in which we want to record the name of the person leading a particular project:
We want to restrict the names that can be entered into cell B1 to a list of valid possibilities. These are stored, in this example, in a range on a second sheet:
First, let’s name this range for convenience. Highlight the range, right-click and choose Name and protect range …, then choose a handy name for the range:
Here I’ve called the range ProjectLeaders. I should probably protect it too, by checking the Protect checkbox, though that’s optional.
Returning now to Sheet1, I can right-click in the cell B1 and select Data Validation…. Here’s the dialog that opens:
As you can see, in the Criteria line I’ve used the option to List from a range and entered the name of our range containing the Project Leaders’ names. Once these options are saved, cell B1 has a down-arrow at the right hand side, which, when clicked, opens a drop-down menu:
We can now only select a valid entry from those provided.