I'm trying to use data validation to limit the range of input for a cell.

I need the user to be able to only enter a number between -10 and 10,
with the option of being able to use one decimal place. However, I don't
want it to be possible to enter a number with the percent symbol, so 5%
would be invalid.

I can get the 'no % symbol' rule to work if I use a validation criteria
of whole number (and minimum -10, maximum 10). However if I use a
validation criteria of 'decimal' (to allow the one decimal place input
which I want) this then allows % symbols to be used, so 5% would be a
valid entry?

Can anyone advise how I can do this? Ideally without VBA.

• Input range -10 to 10
• Decmimals allowed (not necessairily limited to one decimal place)
• Must be just a number - no % symbol


Any help greatly appreciated.

-Jay-

Either on the same spreadsheet or a different spreadsheet, create a list
that uses all the values you need.

Type in -10 in the first cell, -9.9 in the cell under it and and then
highlight both cells. Now use the fill handle on the bottom right corner of
the highlighted cells to drag down to line 201 to auto create the list to
the number 10. Now highlight the entire list just created.

Now click in the Name Box which is just above cell A1 and next to the
Formula Bar. Type a recognisable name for the List without using spaces or
punctuation marks( I used List). Now create the Validation in the cell
required by using the list option. In the source area, type =NameofList
(this is the name you used to name the list). OK out of it after setting any
warning messages.

You normally cannot refer to a different worksheet when using Validation,
but if you use a named range like we did here, then you can refer to a
different worksheet. This does not allow use of the %.

--
Saruman

---------------------------------------------------------------------------
All Outgoing Mail Scanned By Norton Antivirus 2003
---------------------------------------------------------------------------

"Jay" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> I'm trying to use data validation to limit the range of input for a cell.
>
> I need the user to be able to only enter a number between -10 and 10,
> with the option of being able to use one decimal place. However, I don't
> want it to be possible to enter a number with the percent symbol, so 5%
> would be invalid.
>
> I can get the 'no % symbol' rule to work if I use a validation criteria
> of whole number (and minimum -10, maximum 10). However if I use a
> validation criteria of 'decimal' (to allow the one decimal place input
> which I want) this then allows % symbols to be used, so 5% would be a
> valid entry?
>
> Can anyone advise how I can do this? Ideally without VBA.
>
> • Input range -10 to 10
> • Decmimals allowed (not necessairily limited to one decimal place)
> • Must be just a number - no % symbol
>
>
> Any help greatly appreciated.
>
> -Jay-

Excel shouldn't be used for data entry.

EAT **** and learn a real program-- like a database


Saruman wrote:
> Either on the same spreadsheet or a different spreadsheet, create a list
> that uses all the values you need.
>
> Type in -10 in the first cell, -9.9 in the cell under it and and then
> highlight both cells. Now use the fill handle on the bottom right corner of
> the highlighted cells to drag down to line 201 to auto create the list to
> the number 10. Now highlight the entire list just created.
>
> Now click in the Name Box which is just above cell A1 and next to the
> Formula Bar. Type a recognisable name for the List without using spaces or
> punctuation marks( I used List). Now create the Validation in the cell
> required by using the list option. In the source area, type =NameofList
> (this is the name you used to name the list). OK out of it after setting any
> warning messages.
>
> You normally cannot refer to a different worksheet when using Validation,
> but if you use a named range like we did here, then you can refer to a
> different worksheet. This does not allow use of the %.
>
> --
> Saruman
>
> ---------------------------------------------------------------------------
> All Outgoing Mail Scanned By Norton Antivirus 2003
> ---------------------------------------------------------------------------
>
> "Jay" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > I'm trying to use data validation to limit the range of input for a cell.
> >
> > I need the user to be able to only enter a number between -10 and 10,
> > with the option of being able to use one decimal place. However, I don't
> > want it to be possible to enter a number with the percent symbol, so 5%
> > would be invalid.
> >
> > I can get the 'no % symbol' rule to work if I use a validation criteria
> > of whole number (and minimum -10, maximum 10). However if I use a
> > validation criteria of 'decimal' (to allow the one decimal place input
> > which I want) this then allows % symbols to be used, so 5% would be a
> > valid entry?
> >
> > Can anyone advise how I can do this? Ideally without VBA.
> >
> > · Input range -10 to 10
> > · Decmimals allowed (not necessairily limited to one decimal place)
> > · Must be just a number - no % symbol
> >
> >
> > Any help greatly appreciated.
> >
> > -Jay-

That's fantastic. Thanks a lot. Is there any way to do exactly the
same, but *not* have the drop-down control appear when the cell is
entered? The number of entries in the list doesn't lend itself to that
kind of control, and it looks like poor design if a user was to select
the control & see a drop-down with 200 entries.

If this is possible then that would be ideal.

Cheers,

-Jay-





> Either on the same spreadsheet or a different spreadsheet, create a list
> that uses all the values you need.
>
> Type in -10 in the first cell, -9.9 in the cell under it and and then
> highlight both cells. Now use the fill handle on the bottom right corner of
> the highlighted cells to drag down to line 201 to auto create the list to
> the number 10. Now highlight the entire list just created.
>
> Now click in the Name Box which is just above cell A1 and next to the
> Formula Bar. Type a recognisable name for the List without using spaces or
> punctuation marks( I used List). Now create the Validation in the cell
> required by using the list option. In the source area, type =NameofList
> (this is the name you used to name the list). OK out of it after setting any
> warning messages.
>
> You normally cannot refer to a different worksheet when using Validation,
> but if you use a named range like we did here, then you can refer to a
> different worksheet. This does not allow use of the %.
>
> --
> Saruman
>
> ---------------------------------------------------------------------------
> All Outgoing Mail Scanned By Norton Antivirus 2003
> ---------------------------------------------------------------------------
>
> "Jay" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> I'm trying to use data validation to limit the range of input for a cell.
>>
>> I need the user to be able to only enter a number between -10 and 10,
>> with the option of being able to use one decimal place. However, I don't
>> want it to be possible to enter a number with the percent symbol, so 5%
>> would be invalid.
>>
>> I can get the 'no % symbol' rule to work if I use a validation criteria
>> of whole number (and minimum -10, maximum 10). However if I use a
>> validation criteria of 'decimal' (to allow the one decimal place input
>> which I want) this then allows % symbols to be used, so 5% would be a
>> valid entry?
>>
>> Can anyone advise how I can do this? Ideally without VBA.
>>
>> • Input range -10 to 10
>> • Decmimals allowed (not necessairily limited to one decimal place)
>> • Must be just a number - no % symbol
>>
>>
>> Any help greatly appreciated.
>>
>> -Jay-
>
>

Select the cell, and choose Data>Validation
On the Settings tab, remove the check mark from 'In-cell dropdown'
Click OK

However, with or without the dropdown, users would be able to type a %
sign in the cell, if it creates a number in the valid range, e.g. 90%

Also, if you use the Autofill feature to create the series, you'll have
problems typing into the cell, because it adds very small amounts to
some numbers. You can see the amounts if you expand the number of
decimal places to 14.

To eliminate this, you could type -10 in cell A1.
In the second cell, type: =ROUND(A1+0.1,1)
Drag the formula down as far as required to complete the series.

Jay wrote:
> That's fantastic. Thanks a lot. Is there any way to do exactly the
> same, but *not* have the drop-down control appear when the cell is
> entered? The number of entries in the list doesn't lend itself to that
> kind of control, and it looks like poor design if a user was to select
> the control & see a drop-down with 200 entries.
>
> If this is possible then that would be ideal.
>
> Cheers,
>
> -Jay-
>
>
>
>
>
>> Either on the same spreadsheet or a different spreadsheet, create a list
>> that uses all the values you need.
>>
>> Type in -10 in the first cell, -9.9 in the cell under it and and then
>> highlight both cells. Now use the fill handle on the bottom right
>> corner of
>> the highlighted cells to drag down to line 201 to auto create the list to
>> the number 10. Now highlight the entire list just created.
>>
>> Now click in the Name Box which is just above cell A1 and next to the
>> Formula Bar. Type a recognisable name for the List without using
>> spaces or
>> punctuation marks( I used List). Now create the Validation in the cell
>> required by using the list option. In the source area, type =NameofList
>> (this is the name you used to name the list). OK out of it after
>> setting any
>> warning messages.
>>
>> You normally cannot refer to a different worksheet when using Validation,
>> but if you use a named range like we did here, then you can refer to a
>> different worksheet. This does not allow use of the %.
>>
>> --
>> Saruman
>>
>> ---------------------------------------------------------------------------
>>
>> All Outgoing Mail Scanned By Norton Antivirus 2003
>> ---------------------------------------------------------------------------
>>
>>
>> "Jay" <(E-Mail Removed)> wrote in message
>> news:(E-Mail Removed)...
>>
>>> I'm trying to use data validation to limit the range of input for a
>>> cell.
>>>
>>> I need the user to be able to only enter a number between -10 and 10,
>>> with the option of being able to use one decimal place. However, I don't
>>> want it to be possible to enter a number with the percent symbol, so 5%
>>> would be invalid.
>>>
>>> I can get the 'no % symbol' rule to work if I use a validation criteria
>>> of whole number (and minimum -10, maximum 10). However if I use a
>>> validation criteria of 'decimal' (to allow the one decimal place input
>>> which I want) this then allows % symbols to be used, so 5% would be a
>>> valid entry?
>>>
>>> Can anyone advise how I can do this? Ideally without VBA.
>>>
>>> • Input range -10 to 10
>>> • Decmimals allowed (not necessairily limited to one decimal place)
>>> • Must be just a number - no % symbol
>>>
>>>
>>> Any help greatly appreciated.
>>>
>>> -Jay-
>>
>>
>>
>
>


--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

Useful to know Debra, I hadn't seen that happen before.

--
Saruman

---------------------------------------------------------------------------
All Outgoing Mail Scanned By Norton Antivirus 2003
---------------------------------------------------------------------------

"Debra Dalgleish" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Select the cell, and choose Data>Validation
> On the Settings tab, remove the check mark from 'In-cell dropdown'
> Click OK
>
> However, with or without the dropdown, users would be able to type a %
> sign in the cell, if it creates a number in the valid range, e.g. 90%
>
> Also, if you use the Autofill feature to create the series, you'll have
> problems typing into the cell, because it adds very small amounts to
> some numbers. You can see the amounts if you expand the number of
> decimal places to 14.
>
> To eliminate this, you could type -10 in cell A1.
> In the second cell, type: =ROUND(A1+0.1,1)
> Drag the formula down as far as required to complete the series.
>
> Jay wrote:
> > That's fantastic. Thanks a lot. Is there any way to do exactly the
> > same, but *not* have the drop-down control appear when the cell is
> > entered? The number of entries in the list doesn't lend itself to that
> > kind of control, and it looks like poor design if a user was to select
> > the control & see a drop-down with 200 entries.
> >
> > If this is possible then that would be ideal.
> >
> > Cheers,
> >
> > -Jay-
> >
> >
> >
> >
> >
> >> Either on the same spreadsheet or a different spreadsheet, create a
list
> >> that uses all the values you need.
> >>
> >> Type in -10 in the first cell, -9.9 in the cell under it and and then
> >> highlight both cells. Now use the fill handle on the bottom right
> >> corner of
> >> the highlighted cells to drag down to line 201 to auto create the list
to
> >> the number 10. Now highlight the entire list just created.
> >>
> >> Now click in the Name Box which is just above cell A1 and next to the
> >> Formula Bar. Type a recognisable name for the List without using
> >> spaces or
> >> punctuation marks( I used List). Now create the Validation in the cell
> >> required by using the list option. In the source area, type =NameofList
> >> (this is the name you used to name the list). OK out of it after
> >> setting any
> >> warning messages.
> >>
> >> You normally cannot refer to a different worksheet when using
Validation,
> >> but if you use a named range like we did here, then you can refer to a
> >> different worksheet. This does not allow use of the %.
> >>
> >> --
> >> Saruman
> >>
>
>> -------------------------------------------------------------------------
--
> >>
> >> All Outgoing Mail Scanned By Norton Antivirus 2003
>
>> -------------------------------------------------------------------------
--
> >>
> >>
> >> "Jay" <(E-Mail Removed)> wrote in message
> >> news:(E-Mail Removed)...
> >>
> >>> I'm trying to use data validation to limit the range of input for a
> >>> cell.
> >>>
> >>> I need the user to be able to only enter a number between -10 and 10,
> >>> with the option of being able to use one decimal place. However, I
don't
> >>> want it to be possible to enter a number with the percent symbol, so
5%
> >>> would be invalid.
> >>>
> >>> I can get the 'no % symbol' rule to work if I use a validation
criteria
> >>> of whole number (and minimum -10, maximum 10). However if I use a
> >>> validation criteria of 'decimal' (to allow the one decimal place input
> >>> which I want) this then allows % symbols to be used, so 5% would be a
> >>> valid entry?
> >>>
> >>> Can anyone advise how I can do this? Ideally without VBA.
> >>>
> >>> • Input range -10 to 10
> >>> • Decmimals allowed (not necessairily limited to one decimal place)
> >>> • Must be just a number - no % symbol
> >>>
> >>>
> >>> Any help greatly appreciated.
> >>>
> >>> -Jay-
> >>
> >>
> >>
> >
> >
>
>
> --
> Debra Dalgleish
> Contextures
> http://www.contextures.com/tiptech.html
>

Thanks Debra. I hadn't seen the "In-cell dropdown" option, which is a
bit embarrassing considering it's been staring me in the face:-)

I noticed that about adding the .00000000000001s , when I tested the
validation and it wouldn't accept what should have been a value in the
list. Why does it do that?

And the funny thing is I replaced the auto-fill with a very similar
formula to the one you suggested.

Thanks for your help, (I'll look at the screen more closely next time :-)

Regards

Jasom


Debra Dalgleish wrote:
> Select the cell, and choose Data>Validation
> On the Settings tab, remove the check mark from 'In-cell dropdown'
> Click OK
>
> However, with or without the dropdown, users would be able to type a %
> sign in the cell, if it creates a number in the valid range, e.g. 90%
>
> Also, if you use the Autofill feature to create the series, you'll have
> problems typing into the cell, because it adds very small amounts to
> some numbers. You can see the amounts if you expand the number of
> decimal places to 14.
>
> To eliminate this, you could type -10 in cell A1.
> In the second cell, type: =ROUND(A1+0.1,1)
> Drag the formula down as far as required to complete the series.
>
> Jay wrote:
>> That's fantastic. Thanks a lot. Is there any way to do exactly the
>> same, but *not* have the drop-down control appear when the cell is
>> entered? The number of entries in the list doesn't lend itself to
>> that kind of control, and it looks like poor design if a user was to
>> select the control & see a drop-down with 200 entries.
>>
>> If this is possible then that would be ideal.
>>
>> Cheers,
>>
>> -Jay-
>>
>>
>>
>>
>>
>>> Either on the same spreadsheet or a different spreadsheet, create a list
>>> that uses all the values you need.
>>>
>>> Type in -10 in the first cell, -9.9 in the cell under it and and then
>>> highlight both cells. Now use the fill handle on the bottom right
>>> corner of
>>> the highlighted cells to drag down to line 201 to auto create the
>>> list to
>>> the number 10. Now highlight the entire list just created.
>>>
>>> Now click in the Name Box which is just above cell A1 and next to the
>>> Formula Bar. Type a recognisable name for the List without using
>>> spaces or
>>> punctuation marks( I used List). Now create the Validation in the cell
>>> required by using the list option. In the source area, type =NameofList
>>> (this is the name you used to name the list). OK out of it after
>>> setting any
>>> warning messages.
>>>
>>> You normally cannot refer to a different worksheet when using
>>> Validation,
>>> but if you use a named range like we did here, then you can refer to a
>>> different worksheet. This does not allow use of the %.
>>>
>>> --
>>> Saruman
>>>
>>> ---------------------------------------------------------------------------
>>>
>>> All Outgoing Mail Scanned By Norton Antivirus 2003
>>> ---------------------------------------------------------------------------
>>>
>>>
>>> "Jay" <(E-Mail Removed)> wrote in message
>>> news:(E-Mail Removed)...
>>>
>>>> I'm trying to use data validation to limit the range of input for a
>>>> cell.
>>>>
>>>> I need the user to be able to only enter a number between -10 and 10,
>>>> with the option of being able to use one decimal place. However, I
>>>> don't
>>>> want it to be possible to enter a number with the percent symbol, so 5%
>>>> would be invalid.
>>>>
>>>> I can get the 'no % symbol' rule to work if I use a validation criteria
>>>> of whole number (and minimum -10, maximum 10). However if I use a
>>>> validation criteria of 'decimal' (to allow the one decimal place input
>>>> which I want) this then allows % symbols to be used, so 5% would be a
>>>> valid entry?
>>>>
>>>> Can anyone advise how I can do this? Ideally without VBA.
>>>>
>>>> • Input range -10 to 10
>>>> • Decmimals allowed (not necessairily limited to one decimal place)
>>>> • Must be just a number - no % symbol
>>>>
>>>>
>>>> Any help greatly appreciated.
>>>>
>>>> -Jay-
>>>
>>>
>>>
>>
>>
>
>

You're welcome! The problem is caused by a rounding error, which Chip
Pearson describes here:

http://cpearson.com/excel/rounding.htm

Jay wrote:
> Thanks Debra. I hadn't seen the "In-cell dropdown" option, which is a
> bit embarrassing considering it's been staring me in the face:-)
>
> I noticed that about adding the .00000000000001s , when I tested the
> validation and it wouldn't accept what should have been a value in the
> list. Why does it do that?
>
> And the funny thing is I replaced the auto-fill with a very similar
> formula to the one you suggested.
>
> Thanks for your help, (I'll look at the screen more closely next time :-)
>
> Regards
>
> Jasom
>
>
> Debra Dalgleish wrote:
>
>> Select the cell, and choose Data>Validation
>> On the Settings tab, remove the check mark from 'In-cell dropdown'
>> Click OK
>>
>> However, with or without the dropdown, users would be able to type a %
>> sign in the cell, if it creates a number in the valid range, e.g. 90%
>>
>> Also, if you use the Autofill feature to create the series, you'll
>> have problems typing into the cell, because it adds very small amounts
>> to some numbers. You can see the amounts if you expand the number of
>> decimal places to 14.
>>
>> To eliminate this, you could type -10 in cell A1.
>> In the second cell, type: =ROUND(A1+0.1,1)
>> Drag the formula down as far as required to complete the series.
>>
>> Jay wrote:
>>
>>> That's fantastic. Thanks a lot. Is there any way to do exactly the
>>> same, but *not* have the drop-down control appear when the cell is
>>> entered? The number of entries in the list doesn't lend itself to
>>> that kind of control, and it looks like poor design if a user was to
>>> select the control & see a drop-down with 200 entries.
>>>
>>> If this is possible then that would be ideal.
>>>
>>> Cheers,
>>>
>>> -Jay-
>>>
>>>
>>>
>>>
>>>
>>>> Either on the same spreadsheet or a different spreadsheet, create a
>>>> list
>>>> that uses all the values you need.
>>>>
>>>> Type in -10 in the first cell, -9.9 in the cell under it and and then
>>>> highlight both cells. Now use the fill handle on the bottom right
>>>> corner of
>>>> the highlighted cells to drag down to line 201 to auto create the
>>>> list to
>>>> the number 10. Now highlight the entire list just created.
>>>>
>>>> Now click in the Name Box which is just above cell A1 and next to the
>>>> Formula Bar. Type a recognisable name for the List without using
>>>> spaces or
>>>> punctuation marks( I used List). Now create the Validation in the cell
>>>> required by using the list option. In the source area, type =NameofList
>>>> (this is the name you used to name the list). OK out of it after
>>>> setting any
>>>> warning messages.
>>>>
>>>> You normally cannot refer to a different worksheet when using
>>>> Validation,
>>>> but if you use a named range like we did here, then you can refer to a
>>>> different worksheet. This does not allow use of the %.
>>>>
>>>> --
>>>> Saruman
>>>>
>>>> ---------------------------------------------------------------------------
>>>>
>>>> All Outgoing Mail Scanned By Norton Antivirus 2003
>>>> ---------------------------------------------------------------------------
>>>>
>>>>
>>>> "Jay" <(E-Mail Removed)> wrote in message
>>>> news:(E-Mail Removed)...
>>>>
>>>>> I'm trying to use data validation to limit the range of input for a
>>>>> cell.
>>>>>
>>>>> I need the user to be able to only enter a number between -10 and 10,
>>>>> with the option of being able to use one decimal place. However, I
>>>>> don't
>>>>> want it to be possible to enter a number with the percent symbol,
>>>>> so 5%
>>>>> would be invalid.
>>>>>
>>>>> I can get the 'no % symbol' rule to work if I use a validation
>>>>> criteria
>>>>> of whole number (and minimum -10, maximum 10). However if I use a
>>>>> validation criteria of 'decimal' (to allow the one decimal place input
>>>>> which I want) this then allows % symbols to be used, so 5% would be a
>>>>> valid entry?
>>>>>
>>>>> Can anyone advise how I can do this? Ideally without VBA.
>>>>>
>>>>> • Input range -10 to 10
>>>>> • Decmimals allowed (not necessairily limited to one decimal place)
>>>>> • Must be just a number - no % symbol
>>>>>
>>>>>
>>>>> Any help greatly appreciated.
>>>>>
>>>>> -Jay-
>>>>
>>>>
>>>>
>>>>
>>>
>>>
>>
>>


--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html