Cannot use external references with data validation in Excel (211548)


The information in this article applies to:

  • Microsoft Office Excel 2003
  • Microsoft Excel 2002
  • Microsoft Excel 2000
  • Microsoft Excel 97 for Windows
This article was previously published under Q211548

SYMPTOMS

When you type a cell reference to a cell in the Source or Formula box in the Data Validation dialog box, and then click OK, the following message appears:
You may not use references to other worksheets or workbooks for Data Validation criteria.

CAUSE

This behavior occurs when the following conditions are true:
  • You select the cells to which you want to apply Data Validation, and then click Validation on the Data menu.
  • You click the Settings tab, and then click Custom in the Allow list.
  • You specify a cell reference to a cell in another worksheet or workbook in the Source (or Formula) box.
The Data Validation command allows you to place restrictions on data that is typed into specific cells. However, the cells that contain the data criteria can refer only to cells within the same worksheet as the cells that are restricted.

WORKAROUND

You can use the following method to specify a cell that is in an external worksheet as Data Validation criteria if a local cell refers to the criteria cell:
  1. On the File menu, click New, click Workbook, and then click OK.
  2. Select cell A1.
  3. On the Data menu, click Validation, and then click the Settings tab.
  4. In the Allow list, click Whole number.
  5. In the Data list, click equal to.
  6. In the Value box, type a reference to a cell on the worksheet; for example, type =$B$1.
  7. Click OK.
  8. In the cell you referenced in step 6, type a formula that refers to the external criteria cell. For example, in cell B1, type the following formula:

    =Sheet2!$C$1

  9. In the external cell, type the criteria value you want to use for Data Validation. For example, in cell C1 of Sheet2, type 5.
  10. You may now type only the Data Validation criteria (for example, 5) in cell A1 of Sheet1.

MORE INFORMATION

Microsoft Excel includes a tool that allows you to specify what data is valid for individual cells or cell ranges in a worksheet. This tool is called Data Validation. To access the tool, click Validation on the Data menu.

Restrictions include values, dates, times, or lists of text or values, and can be limited to exact matches or ranges of cells. You can type the validating values in the Data Validation dialog box, or you can store them in worksheet cells. These validating cells must be on the same worksheet as the cells being restricted.

REFERENCES

For more information about Data Validation, click Microsoft Excel Help on the Help menu, type Specify the valid entries for cells in the Office Assistant or the Answer Wizard, and then click Search to view the topic.
Modification Type:MinorLast Reviewed:8/28/2006
Keywords:kbpending kbprb KB211548
©2004 Microsoft Corporation. All rights reserved.