XL97: Cannot Use Custom Function with Data Validation

Last reviewed: March 13, 1998
Article ID: Q160523
The information in this article applies to:
  • Microsoft Excel 97 for Windows

SYMPTOMS

When you use custom criteria with data validation and attempt to type a custom function in the Formula box in the Data Validation dialog box, the following message appears:

   A named range you specified cannot be found.

CAUSE

This behavior occurs when the following conditions are met:

  • You click Validation on the Data menu, and then click the Settings tab.

    -and-

  • You click Custom in the Allow box.

    -and-

  • You type a custom function in the Formula box.

When you use custom criteria for data validation, Microsoft Excel accepts only a built-in formula or a defined name in the Formula box.

WORKAROUND

To work around this behavior, type the custom function in a cell on the worksheet, and then type a formula that refers to this cell in the Formula box. For example, type the custom function in cell A1, and then type "=A1" (without the quotation marks) in the Formula box in the Data Validation dialog box.

STATUS

This behavior is by design of Microsoft Excel 97 for Windows.

MORE INFORMATION

The Custom data validation criteria type allows you to type a formula, use an expression, or refer to a calculation in another cell to determine a valid entry. When you type a formula, the formula must evaluate to True or False.

For additional information about data validation, please see the following article in the Microsoft Knowledge Base:

   ARTICLE-ID: Q159252
   TITLE     : XL97: Description and Examples of Data Validation

REFERENCES

For more information about data validation data types, click the Index tab in Microsoft Excel Help, type the following text

   Data Validation, Data Type

and then double-click the selected text to go to the "Types of data restrictions" topic.


Additional query words: XL97 8.00
Keywords : xlvbainfo xlformula
Version : WINDOWS:97
Platform : WINDOWS


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: March 13, 1998
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.