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

ID: Q188661

The information in this article applies to:

Microsoft Excel 97 for Windows

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:

   ARTICLE-ID: Q120802
   TITLE     : 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:

   ARTICLE-ID: Q159252
   TITLE     : XL97: Description and Examples of Data Validation

Additional query words: XL97 option checkbox choice survey

Keywords          : kbdta xlui xlformula 
Version           : WINDOWS:97
Platform          : WINDOWS
Issue type        : kbhowto

Last Reviewed: November 5, 1998