Restricting Excel Cells to a List and Showing Drop-Down

If the list of values is located on the same sheet:

1. Select cells you want to attach list to
2. Select Data > Data Validation
3. In the Data Validation dialog, Settings tab, set:
   Allow: List
   Source: [range, that represents list of values in the list, e.g. “=A1:A5“]
4. Click OK

In many cases, however, it’s more convenient to store list contents on separate sheet. But if you try to specify Source from a different spreadsheet (e.g. “=Sheet2!A1:A5“), Excel will throw an error:  “The formula you typed contains an error. <…>“. Apparently Excel does not allow range from a different spreadsheet. But there’s a nice workaround, which I digged out of internet long time ago, and can’t remember the source. What you need to do, is to create a named range, and use it instead of the cell range.

Creating a named range:

1. Go to a sheet where list is located, and select all list items.
2. Right click on selected cells and choose Name a Range… or select Formulas > Define Name
3. Specify Name for the range (e.g. ‘MyList‘) and verify that Refers to range is correct.
4. Click OK.

Now you can use a named range you just created in the Data Validation:

1. Select cells you want to attach list to
2. Select Data > Data Validation
3. In the Data Validation dialog, Settings tab, set:
   Allow: List
   Source: [equal sign and the name of the range you just created “=MyList“]
4. Click OK