DataValidationProblem
 

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

DataValidationProblem
 

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


"Mike" wrote in message
...
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
even though they are on the validation list.
I am confused over this. Can anyone help?

DataValidationProblem
 

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


"Mike" wrote in messag
..
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
even though they are on the validation list
I am confused over this. Can anyone help

DataValidationProblem
 

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.

--
Dick Kusleika
MVP - Excel
Excel Blog - Daily Dose of Excel
www.dicks-blog.com

Mike wrote:
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. 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.4 all the way up to 40.0?

--
Regards,
Tom Ogilvy


"Mike" wrote in message
...
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
even though they are on the validation list.
I am confused over this. Can anyone help?

DataValidationProblem
 

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

"Mike" wrote in message
...
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.
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.4
all the way up to 40.0?

--
Regards,
Tom Ogilvy


"Mike" wrote in message
...
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
even though they are on the validation list.
I am confused over this. Can anyone help?

DataValidationProblem
 

DataValidationProblem
 

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

--
Regards
Tom Ogilv

"Mike" wrote in messag
..
To
Thanks for responding. Yes, the data set has the values listed in 0.
increments
Another strange aspect is that it will allow entries of 1.2 and 1.3 a
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 i
though the cell drop down list
I am completely confused
Mik
----- Tom Ogilvy wrote: ----
in the validation list textbox you have this listed lik
0.0,0.1,0.2,0.3,0.
all the way up to 40.0
--
Regards
Tom Ogilv
"Mike" wrote in messag
..
I hope someone can help with this "Mystery
My goal it to use data validation - list to allow an operator t
enter
numerical value into a cell either by pickin
a value from the drop down list or manually type a value that is o
th
data validation list. My purpose is to avoi
the operator entering labels such as "77 years" instead of th
number 77
I have one cell that the data list is 20 to 99. The operator ca
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
even though they are on the validation list
I am confused over this. Can anyone help

DataValidationProblem
 

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
To
Thanks for responding. Yes, the data set has the values listed in 0.
increments. Another strange aspect is that it will allow entries o
1.2 and 1.3 as well as 0, 0.1 and 0.2. But no other entries ar
allowed if manually typed in. The only way to enter any other value
from the data validation list is though the cell drop down list. I a
completely confused
Mik
----- Tom Ogilvy wrote: ----
in the validation list textbox you have this listed lik
0.0,0.1,0.2,0.3,0.4 all the way up to 40.0
-
Regards
Tom Ogilv
"Mike" wrote in messag
..
I hope someone can help with this "Mystery
My goal it to use data validation - list to allow an operator t
enter a numerical value into a cell either by pickin
a value from the drop down list or manually type a value that i
on the data validation list. My purpose is to avoi
the operator entering labels such as "77 years" instead of th
number 77. I have one cell that the data list is 20 to 99. Th
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.
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