Error checking a cell?

  • Thread starter Thread starter barman
  • Start date Start date
B

barman

I want to do error checking on a data entry cell and can't figure out
how to do it without an illegal circular reference. I don't want to
have hidden cells. Let's say that I am entering data in a cell that is
always between 1 and 10. I want to be sure that if I introduce a typo
with the decimal point like 83 that the cell corrects this to be 8.3 .
How can I do that? Any IF statement on a cell itself results in a
circular reference.

For example, I can't come up with a statement for a cell, say B23,
that says something like

=IF(LEN(B23)=0,"", ...

Is it possible? Thanks.

Dilip
 
You can use data validation to do this. It won't correct an invalid entry
but it won't allow you to enter an invalid entry.

For example, to allow only numeric values from 1 to 10...

Select the cell in question (assume it's B23)
Goto the menu Data>Validation
Allow: Custom
Formula: =AND(B23>=1,B23<=10)
OK

If you attempt to type in anything other than a number from 1 to 10 you'll
get an error message.

However, you can still copy/paste, drag and drop into this cell and you will
not get an error.
 
Thanks! I would like the data to be corrected instead of just
validated. Is that possible?

Dilip
 
How many digits maximum are permitted after the decimal point? In other
words, is 8.35 or 8.333 valid entries?

--
Rick (MVP - Excel)


Thanks! I would like the data to be corrected instead of just
validated. Is that possible?

Dilip
 
I'd like as one example when I enter something like 1006, it should
reset and be 100.6 - it is a weight with one decimal point that should
be between 90 and 120 pounds. It's too easy to enter 1006 and miss the
decimal point. Thanks!

Dilip
 
You will need to use VB code to accomplish this. Here is a worksheet Change
event procedure which will do what you want...

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo FixItUp
If Target.Column = 2 Then
Application.EnableEvents = False
If InStr(Val(Target.Value), ".") = 0 Then
Target.Value = Target.Value / 10
End If
End If
FixItUp:
Application.EnableEvents = True
End Sub

In case you are not familiar with VB, here is how you would install the
above code. Right click the tab at the bottom of the worksheet you want this
functionality on, select View Code from the popup menu that appears and
copy/paste the above code into the code window that opened up when you did
that. Next, change the 1 in this statement...

If Target.Column = 2 Then

to the number of the column you want to have this functionality. That's it.
Go back to your worksheet and type number in with and without decimal points
and see if this is what you wanted.

--
Rick (MVP - Excel)


I'd like as one example when I enter something like 1006, it should
reset and be 100.6 - it is a weight with one decimal point that should
be between 90 and 120 pounds. It's too easy to enter 1006 and miss the
decimal point. Thanks!

Dilip
 
Wow, that's great! I will try playing with this code. I do have titles
at the top of columns - as long as I don't try to change the titles, I
presume this will work. Thanks!
 
I forgot about headers. Try changing this line...

If Target.Column = 2 Then

to this...

If Target.Column = 2 And Target.Row > 1 Then

This will allow you to change your headers if you want. Actually, as long as
your headers do not have a dot in them, the code would have worked as is...
but it is better to changed it as above to be sure.

--
Rick (MVP - Excel)


Wow, that's great! I will try playing with this code. I do have titles
at the top of columns - as long as I don't try to change the titles, I
presume this will work. Thanks!
 
Back
Top