XL97: How to Restrict Numeric Entry to Only One Cell in a Range (188661)


The information in this article applies to:

  • Microsoft Excel 97 for Windows
This article was previously published under Q188661

SUMMARY

This article shows you how to use data validation to ensure that a number can be entered into only one cell in a range that you specify.

MORE INFORMATION

By using a data validation formula, you can ensure that a number can be entered into only one cell in a range that you specify. The data validation formula should look as follows 
   =SUM(<RANGE>)
where <RANGE> is the range you want to restrict.

For example, if you want to allow a number to be entered into either cell A2 or B2, but not both, follow these steps:

  1. Select cells A2 and B2.
  2. On the Data menu, click Validation, and then click the Settings tab.
  3. In the Allow list, click Decimal.

    NOTE: This will prevent you form entering a text value into the cells to which you apply the validation rule.
  4. In the Data list, click Equal To.
  5. In the Value box enter the following formula: 
           =SUM($A$2:$B$2)
    NOTE: This reference must be absolute.
  6. Click OK.
  7. Enter a numeric value other than zero in cell A2 (zero is exempt from the validation rule).
  8. Enter a numeric value other than zero in cell B2.

    Note that you receive a Stop Alert message because you already have a value in cell A2. To enter a number into cell B2, you must first clear the value in cell A2.
  9. Click Cancel.
  10. Clear the contents of cell A2 by pressing the DELETE key.
  11. Re-enter a numeric value other than zero in cell B2.
Note that you do not receive an error message because there is no longer a value in cell A2.

REFERENCES

For more information about data validation, click the Office Assistant, type data validation, click Search, and then click to view "Prevent entry of incorrect data in a cell."

NOTE: If the Assistant is hidden, click the Office Assistant button on the Standard toolbar. If Microsoft Help is not installed on your computer, please see the following article in the Microsoft Knowledge Base:

120802 Office: How to Add/Remove a Single Office Program or Component

For additional examples of data validation, please see the following article in the Microsoft Knowledge Base:

159252 XL97: Description and Examples of Data Validation

Modification Type:MajorLast Reviewed:10/21/2000
Keywords:kbhowto KB188661
©2002 Microsoft Corporation. All rights reserved.