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?
 

Some videos you may like

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

Brian from Maui

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

Watch MrExcel Video

Forum statistics

Threads
1,118,909
Messages
5,574,979
Members
412,630
Latest member
Eireangel
Top