Microsoft KB Archive/188661

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

Article ID: 188661

Article Last Modified on 10/21/2000

-

APPLIES TO


 * Microsoft Excel 97 Standard Edition

-



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() where  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. </li> Click OK.</li> Enter a numeric value other than zero in cell A2 (zero is exempt from the validation rule).</li> 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.</li> Click Cancel.</li> Clear the contents of cell A2 by pressing the DELETE key.</li> Re-enter a numeric value other than zero in cell B2.</li></ol>

Note that you do not receive an error message because there is no longer a value in cell A2.

<div class="references_section">