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

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

Brian from Maui

MrExcel MVP
Joined
Feb 16, 2002
Messages
8,459
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.
 

thejd99

Board Regular
Joined
Mar 19, 2002
Messages
57
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.
 

thejd99

Board Regular
Joined
Mar 19, 2002
Messages
57
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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,498
Messages
5,832,055
Members
430,109
Latest member
govivek

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
Top