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
Advertisements
Restricting Excel Cells to a List and Showing Drop-Down

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s