Microsoft KB Archive/188662

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

Article ID: 188662

Article Last Modified on 9/11/2002

-

APPLIES TO


 * Microsoft Excel 98 for Macintosh

-



This article was previously published under Q188662





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.

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

<div class="references_section">