XL98: Cannot Use External References with Data Validation

Last reviewed: February 2, 1998
Article ID: Q179859
The information in this article applies to:
  • Microsoft Excel 98 Macintosh Edition

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.

The 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

To work around this problem, use a local cell to refer to the criteria cell. To do this, use one of the following methods.

Method 1: Use a Local Cell that Refers to the External 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 click the Settings tab.

  4. In the Allow box, click Whole number.

  5. In the Data box, click Equal To.

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

  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 the number 5.

    You can type only the data validation criteria (for example 5) in cell A1 of Sheet1.

Method 2: Use INDIRECT to Return a Value in the External 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 click the Settings tab.

  4. In the Allow box, click Whole number.

  5. In the Data box, click Equal To.

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

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

  7. Click OK.

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

You can type only the data validation criteria (for example 10) in cell A1 of Sheet1.

MORE INFORMATION

Microsoft Excel 98 Macintosh Edition allows you to specify what data is valid for individual cells or for cell ranges in a worksheet. This is called data validation. To use data validation, 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 valid 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 want to restrict.

REFERENCES

For more information about data validation, click the Index button in Microsoft Excel Help, and type the following text:

   data validation, overview

and then double-click the selected text to go to the "About defining the valid entries for a cell" topic.


Additional query words: XL98 8.00
Keywords : xlformula xlui kbui
Version : MACINTOSH:98
Platform : MACINTOSH
Issue type : kbprb
Solution Type : kbnofix


THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: February 2, 1998
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.