Data Validation Advice

[Jay]

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-

 

[Saruman]

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 %.

 

[aaron.kempf]

Excel shouldn't be used for data entry.

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

 

[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-

 

[Debra Dalgleish]

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.

 

[Saruman]

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

 

[Jay]

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]

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

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