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

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 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.