Help with drop down box in Excel

shopndrop

I have a spreadsheet that various staff need to input information into, but I want them all to use the correct spelling of people and places. I thought about using a drop down box for them to choose from which I have set up, but you are able to either choose from the drop down box or type in any text you want into the spreadsheet. I don't want them to be able to put anything other than what is in the drop down box. When I go to data validation, error alert tab, the box is ticked so I thought this would stop anything else being typed in but it isn't. Can anyone tell me what I am doing wrong please?

rmg1

You're right to be using Data/validation. Make a list of the entries you want people to use in a (hidden) column(s).
Then go to Data/Validation and choose "List", you'll need to do one for each type of entry.
Then select the cells you want for that particular list and make sure the "In-cell dropdown" box is ticked.
Should solve your problems.

shopndrop

Will give that a try now. Thanks.

shopndrop

Thanks. This has worked. What I had done previously was to have the lists on another worksheet in same spreadsheet and named the cells but this wasn't working - presumably if I do this again, I need to have the lists on the same worksheet as the drop down box?

rmg1

Unfortnately yes. Validation only works on the cells on the same worksheet. I have tried finding ways round it, but no luck so far.

jamalfatty

Just put the values in one of the far corners and set text colour to white, no-one need know they are there!

More4me

Create your list in another worksheet and give the range a name, say Mylist.
Now select the cell where you want the dropdown list
Data/validation/setting--validation criteria List and in source box type =Mylist.
Hope this helps

rmg1

More4me wrote: »

Create your list in another worksheet and give the range a name, say Mylist.
Now select the cell where you want the dropdown list
Data/validation/setting--validation criteria List and in source box type =Mylist.
Hope this helps

Good point, never thought of using a named range. Could be a little akward updating it if you need to add something, though.

shopndrop

More4me wrote: »

Create your list in another worksheet and give the range a name, say Mylist.
Now select the cell where you want the dropdown list
Data/validation/setting--validation criteria List and in source box type =Mylist.
Hope this helps

This is what I did originally, but it would let me either type in what I wanted or choose from the drop down box. The error message did not appear as I expected it to when I didn't choose something from the dropdown list.

Fairwinds

The named range list should work the same way as the cell reference.

Be aware that neither are full proof as you can still copy and paste any value you like into the cells