XL97: Cannot Use External References with Data Validation

• Microsoft Excel 97 for Windows

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 problem occurs when the following conditions are true:

• You select the cells to which you want to apply Data Validation, and click Validation on the Data menu.

      -and-
  

• You click the Settings tab, and click Settings (or Custom) in the Allow list.

      -and-
  

• You specify a cell reference to a cell in another worksheet or workbook in the Source (or Formula) box.

WORKAROUND

You can specify a cell that is in an external worksheet as Data Validation criteria if a local cell refers to the criteria cell using one of the two methods below:

Method 1: Using a Local Cell that Refers to the External Cell

          a. On the File menu, click New, click Workbook, and then click
             OK.

          b. Select cell A1.

          c. On the Data menu, click Validation and click the Settings tab.

          d. In the Allow box, click Whole number.

          e. In the Data box, click Equal to.

          f. In the Value box, type a reference to a cell on the
             worksheet, for example, type "=$B$1" (without the quotation
             marks).

          g. Click OK.

          h. In the cell you referenced in step f, type a formula that
             refers to the external criteria cell. For example, in cell B1,
             type the following formula:

                =Sheet2!$C$1

          i. In the external cell, type the criteria value you want to use
             for Data Validation. For example, in cell C1 of Sheet2, type
             the number 5.

             You may now type only the Data Validation criteria (for
             example 5) in cell A1 of Sheet1.

          a. On the File menu, click New, click Workbook, and then click
             OK.

          b. Select cell A1.

          c. On the Data menu, click Validation and click the Settings tab.

          d. In the Allow box, click Whole number.

          e. In the Data box, click "Equal to."

          f. In the Value box, type the following formula:

                =Indirect("Sheet2!$C$1")

          g. Click OK.

          h. In cell C1 of Sheet2, type the number 10.

             You may now type only the Data Validation criteria (for
             example 10) in cell A1 of Sheet1.

MORE INFORMATION

Microsoft Excel 97 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 the Index tab in Microsoft Excel Help, type the following text:

   data validation, overview

Or, click the Index tab in Microsoft Excel Help, type the following text:

   data validation, drop-down list