Oct 13 2006, 11:54 AM
I want to limit a user to only enter a number that is 3 number a "." and 2 more numbers, such as 811.20. How can I do this?
I tried Custom and the formual as 3 & "." & 2.
Oct 13 2006, 12:02 PM
A Validation rule can do this. Select the relevant cells. Then click Data--->Validation... Set Allow to Decimal. Set Data to Between. Set the Minimum and Maximum values as you want. I used 100 for the Minimum and 999.99 for the Maximum. You'll also need to format the cell so it shows 2 decimals.
Oct 13 2006, 12:26 PM
There are no minimum Maximun values. Actually they can even use v81.30, these are diagnosis codes.
Oct 13 2006, 12:31 PM
I would probably separate out the left and right side of this number, because it seems they represent different things. i.e.: 822.30
You can then limit the field length to 3 for the txtHead field and 2 for the txtTail field.
This is in line with normalization rules and may help you do sorting/grouping later, as well.
Oct 13 2006, 12:35 PM
The way I do this is just keep a table of all the IDC9's. Then match the entry to the lookup table. You can even use a combo box and let them pick from a list that contains the description.
Oct 13 2006, 12:36 PM
So I should write in forumlas that grab the numbers before the "." and the numbers after the "." and if either of those are 3 or 2 length then it pops up an error message?
Seems like a lot of work to do all of that.
Oct 13 2006, 01:15 PM
Oh, I see. Then the only way to do this would be as Walter has posted. Put the possible codes in consecutive cells in a column. Assign a range name to those cells. Use the range name as the source of Validation Rule of List.
This will also be a lot of work, but there's no way to do this without a lot of work.
Oct 13 2006, 01:46 PM
Yeah, Walter's solution seems the most practical in your case.
Oct 13 2006, 01:48 PM
Oops didn't realize I was in an excel forum.... My first comment is a little out of place, now..!!
Oct 16 2006, 07:25 AM
Ha ha ha.
Too true Troul!
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here