Full Version: Data Validation Question....
Sosul
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.
fkegley
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.
Sosul
There are no minimum Maximun values. Actually they can even use v81.30, these are diagnosis codes.
Troulcortex
I would probably separate out the left and right side of this number, because it seems they represent different things. i.e.: 822.30

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.

hth,

Gabe
niesz
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.
Sosul
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.
fkegley
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.
Troulcortex
Yeah, Walter's solution seems the most practical in your case.

Gabe
Troulcortex
Oops didn't realize I was in an excel forum.... My first comment is a little out of place, now..!!

Sorry!

Gabe
Sosul
Ha ha ha.

Too true Troul!