Select the product you need help with

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

Article ID: 188661 - View products that this article applies to.

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.

Back to the top | Give Feedback

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:

Select cells A2 and B2.
On the Data menu, click Validation, and then click the Settings tab.
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.
In the Data list, click Equal To.
In the Value box enter the following formula:
=SUM($A$2:$B$2)
NOTE: This reference must be absolute.
Click OK.
Enter a numeric value other than zero in cell A2 (zero is exempt from the validation rule).
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.
Click Cancel.
Clear the contents of cell A2 by pressing the DELETE key.
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.

Back to the top | Give Feedback

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

(http://support.microsoft.com/kb/120802/EN-US/ )

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

(http://support.microsoft.com/kb/159252/EN-US/ )

XL97: Description and Examples of Data Validation

Back to the top | Give Feedback

Properties

Article ID: 188661 - Last Review: October 21, 2000 - Revision: 1.0

APPLIES TO

Microsoft Excel 97 Standard Edition

kbhowto KB188661

Retired KB Content Disclaimer

This article was written about products for which Microsoft no longer offers support. Therefore, this article is offered "as is" and will no longer be updated.

Back to the top | Give Feedback