drop down list

HSOLDO

Board Regular
Joined
Sep 3, 2007
Messages
125
I made a cell that works with data validation-drop down list. How to limit users to input into those cells only using drop down lists and not by writing themselves?
now I have a problem that Lets say I have a drop down list(apples,bananas) but sometime users write oranges for example, and I want to stop that.

Thank You
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
No,

Sorry to explain badly.

They just write "oranges" into cell where they should use drop down arrow and select what is in the list. This is ofcourse just some simplified explanation.
Problem for me is that when I use pivot later I dont have unified data so sometime I have things like (apples, apple, aplle,aple.... and millions of wrong combinations) I just want to limit them to use drop down on what is in the source list.
 
Upvote 0
hmmm...that's very odd. If the data validation has been set up correctly then it will display an error message if they try to type in anything other than what is in your list.
 
Upvote 0
can you send me steps (maybe I make mistake somewhere)

I go like this:

sheet1 is source sheet
A1 to A10 I input names
I select A1 to A10
name range to "NAMES"


I go to sheet2

click on for example B10
data validation
allow:list
mark drop down list and ignore blanks
source: =names

thats it
 
Upvote 0
I got it. I must use source:A1:A10 and then it works. I guess range name cannot be used.

Thanks for guiding me to solution :)
 
Upvote 0
That process is correct...however I think I've just found a work around. If they type 'oranges' in another cell and copy it into the cell which has your validation list - it overwrites it and allows 'oranges' in that cell.

I used a named range and it worked fine.
 
Upvote 0

Forum statistics

Threads
1,203,060
Messages
6,053,305
Members
444,651
Latest member
markkuznetsov1

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top