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.

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.

There are no minimum Maximun values. Actually they can even use v81.30, these are diagnosis codes.

I would probably separate out the left and right side of this number, because it seems they represent different things. i.e.: 822.30

txtHead txtTail
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

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.

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.

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.

Yeah, Walter's solution seems the most practical in your case.

Gabe

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

Sorry!

Gabe

Ha ha ha.

Too true Troul!