DataValidationProblem

[Guest]

I hope someone can help with this "Mystery
My goal it to use data validation - list to allow an operator to enter a numerical value into a cell either by pickin
a value from the drop down list or manually type a value that is on the data validation list. My purpose is to avoi
the operator entering labels such as "77 years" instead of the number 77
I have one cell that the data list is 20 to 99. The operator can either enter from list OR type in whole value- there i
no problems with this cell accepting this
I have another cell that data list values are from 0 to 40 in 0.1 increments. I can pick a value from the drop dow
list BUT when manually typing a numerical value, I have a problem
I can enter ONLY the values 0, 0.1 or 0.2 and it will accept OK. But value higher such as 0.3, 1.2 give error messag
even though they are on the validation list
I am confused over this. Can anyone help

 

[Tom Ogilvy]

in the validation list textbox you have this listed like 0.0,0.1,0.2,0.3,0.4
all the way up to 40.0?

--
Regards,
Tom Ogilvy

I hope someone can help with this "Mystery"
My goal it to use data validation - list to allow an operator to enter a

numerical value into a cell either by picking

a value from the drop down list or manually type a value that is on the

data validation list. My purpose is to avoid

the operator entering labels such as "77 years" instead of the number 77.
I have one cell that the data list is 20 to 99. The operator can either

enter from list OR type in whole value- there is

no problems with this cell accepting this.
I have another cell that data list values are from 0 to 40 in 0.1

increments. I can pick a value from the drop down

list BUT when manually typing a numerical value, I have a problem.
I can enter ONLY the values 0, 0.1 or 0.2 and it will accept OK. But value

higher such as 0.3, 1.2 give error message

 

[Guest]

To
Thanks for responding. Yes, the data set has the values listed in 0.1 increments
Another strange aspect is that it will allow entries of 1.2 and 1.3 as well as 0, 0.1 and 0.2
But no other entries are allowed if manually typed in
The only way to enter any other values from the data validation list is though the cell drop down list
I am completely confused
Mike

----- Tom Ogilvy wrote: ----

in the validation list textbox you have this listed like 0.0,0.1,0.2,0.3,0.
all the way up to 40.0

--
Regards
Tom Ogilv

I hope someone can help with this "Mystery
My goal it to use data validation - list to allow an operator to enter

numerical value into a cell either by pickin

a value from the drop down list or manually type a value that is on th

data validation list. My purpose is to avoi

the operator entering labels such as "77 years" instead of the number 77
I have one cell that the data list is 20 to 99. The operator can eithe

enter from list OR type in whole value- there i

no problems with this cell accepting this
I have another cell that data list values are from 0 to 40 in 0.

increments. I can pick a value from the drop dow

list BUT when manually typing a numerical value, I have a problem
I can enter ONLY the values 0, 0.1 or 0.2 and it will accept OK. But valu

higher such as 0.3, 1.2 give error messag

 

[Dick Kusleika]

Mike

In your list, instead of =A1+.1 filled down, but =ROUND(A1+.1,1) and fill
that down. Because there is small error when converting the decimal to
binary and back, by the time you get to .3, the error has accumulated enough
to register.

 

[Tom Ogilvy]

If Dick has provided a useful answer, then the answer to my question was
no - I am using a reference to a range.

--
Regards,
Tom Ogilvy

Tom
Thanks for responding. Yes, the data set has the values listed in 0.1 increments.
Another strange aspect is that it will allow entries of 1.2 and 1.3 as well as 0, 0.1 and 0.2.
But no other entries are allowed if manually typed in.
The only way to enter any other values from the data validation list is

though the cell drop down list.

 

[Guest]

To
Thanks for the assistance. Dicks suggestion of using the "rounding" function solved my problem
Mik

----- Tom Ogilvy wrote: ----

If Dick has provided a useful answer, then the answer to my question wa
no - I am using a reference to a range

 

[Guest]

Dic
Thanks, your solution worked perfectly
Mik

----- Dick Kusleika wrote: ----

Mik

In your list, instead of =A1+.1 filled down, but =ROUND(A1+.1,1) and fil
that down. Because there is small error when converting the decimal t
binary and back, by the time you get to .3, the error has accumulated enoug
to register

--
Dick Kusleik
MVP - Exce
Excel Blog - Daily Dose of Exce
www.dicks-blog.co

Mike wrote