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

Importing data from Excel to SQL Server

Different ways to accomplish this are described in Microsoft KB article #321686. If importing is not recurring, or persistent connection is not possible, it makes sense to use Distributed Query. Running such query in SQL Server Management Studio of SQL Server 2005 will, however, produce the following error:

SQL Server blocked access to STATEMENT 'OpenRowset/OpenDatasource' of component 'Ad Hoc Distributed Queries' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ad Hoc Distributed Queries' by using sp_configure. For more information about enabling 'Ad Hoc Distributed Queries', see "Surface Area Configuration" in SQL Server Books Online.

It can be resolved by enabling “advanced options” and “Ad Hoc distributed Queries” option, executing query to import data, and (optionally) reverting the configuration to its original state:

# First, enable advanced options
sp_configure ‘show advanced options’, 1
reconfigure
# Second, enable execution of Ad Hoc queries
sp_configure ‘Ad Hoc Distributed Queries’, 1
reconfigure
# Import data from Excel to SQL table
SELECT * INTO TableName FROM OPENROWSET(‘Microsoft.Jet.OLEDB.4.0’,
‘Excel 8.0;Database=Location\Name.xls’, [SheetName$])
# Revert both options back: first disable Ad Hoc distributed queries
sp_configure ‘Ad Hoc Distributed Queries’, 0
reconfigure
# … And hide advanced options
sp_configure ‘show advanced options’, 0
reconfigure
Importing data from Excel to SQL Server