Data Validation list

thejd99

Board Regular
Joined
Mar 19, 2002
Messages
57
I must be missing something here. I set up Data Validation with a drop down list. (If it makes any difference the list is on a separate sheet.) The drop down list works fine, I can pick any item from the list to populate the cell. My problem is that if I click on the cell and start typing it allows me to put anything I want in the cell, regardless of whether it's in my list. What am I doing wrong?
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
thejd99 said:
I must be missing something here. I set up Data Validation with a drop down list. (If it makes any difference the list is on a separate sheet.) The drop down list works fine, I can pick any item from the list to populate the cell. My problem is that if I click on the cell and start typing it allows me to put anything I want in the cell, regardless of whether it's in my list. What am I doing wrong?

Check the Error Alert Tab and see if it's set to STOP.

Also by typing in anything, does it let you press Enter and store the value in the cell.
 
Upvote 0
Check the Error Alert Tab and see if it's set to STOP.

It is set to "Stop". But that only seems to control the icon that shows up with the error message - which by the way the error message doesn't even show up when I enter invalid data.

Also by typing in anything, does it let you press Enter and store the value in the cell.

Yes I can enter the data, press enter, leave the cell.
 
Upvote 0
I found my problem after a little digging in the Excel help. Here's what it was:

"Note If your allowed values are based on a cell range with a defined name, and there is a blank cell anywhere in the range, setting the Ignore blank check box allows any values to be entered in the validated cell. This is also true for any cells referenced by validation formulas: if any referenced cell is blank, setting the Ignore blank check box allows any values to be entered in the validated cell."

Thanks anyway
 
Upvote 0

Forum statistics

Threads
1,213,513
Messages
6,114,072
Members
448,546
Latest member
KH Consulting

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