Error-trapping in Excel.

[James Perry]

A data-entry operator keys in numbers from a stack of papers onto an Excel worksheet.
All the numbers are 6-digits.
But to err is human.......
Q1-How to prevent a 6-digit number from being wrongly keyed in as 5-digit or 7-digit??
E.g....123456 could be wrongly entered as 1233456 (that's 7 digits).
Or..... 123456 could be wrongly entered as 12345 (that's 5 digits).
In both cases, Excel must reject the data entry based on an Excel-formula +
display a pop-out error message box that says: 'Invalid Entry'.
Q2-How do i copy the formula from one cell to about 20,000 cells.
Million thanks.

 

[Hui]

James
Select the range, I'll assume it is F2:F20000
Goto the Data, Data Validation tab
Allow, Custom
=AND(LEN(F2)=6,INT(F2)=F2)

Setup messages on the "Error Alert" Tab as appropriate
The above will only allow Integers with length 6 to be entered

 

[NARAYANK991]

Hi James ,

You can use Data Validation for this.

Select your entire data range ; can I assume that this data entry is in one column ? Or is it distributed over several columns ?

Suppose we assume that your data entry range is A2 through Z10000.

Select the range A2 through Z10000 , and click on Data Validation.

Select Data Validation , Custom , and in the formula box enter :

=AND(A2>=100000,A2<=999999)

Click on the Input Message and Error Alert tabs , and enter the desired input message prompt and invalid input warning message , and click OK.

Narayan

 

[James Perry]

Thank you Excel Ninjas. That was quick.! Both solutions are really smart.
Stay blessed.

 

[NARAYANK991]

Hi James ,

I think you should go with Hui's solution , since it checks that the user has entered whole numbers ; my solution does not check for this , and it is possible for the user to enter decimal numbers.

Narayan

 

[James Perry]

@ Narayan: Thanks.