Hi everyone:

I have a number of worksheets in my workbook. On my first worksheet, I have
created a drop-down list with options that are specified on another worksheet
in the same book. I did this by creating a list on the other sheet,
highlighting it, and giving it a name in the name box (Let's call it
'namelist'). On the first sheet, I highlighted a range of cells, selected
Data/Validation, and on the Validation criteria I selected List, and for a
source I put =namelist. It works fine. The problem is, sometimes when I
choose from the drop down list on the first worksheet, it only allows me to
choose from the list, no other option is possible. This is what I want. But
sometimes, I can choose from the list or put in any value I want--which I
definitely do not want. For the life of me I can't figure out why some drop
down lists function one way, and some the other. They appear to have
identical criteria and yet this problem continues to occur. Hopefully I've
explained this properly. Any suggestions greatly appreciated. Thank you.

Besides the criteria, you have to specify the action Excel will take when the
validation fails. From the Data Validation dialog, check the Error Alert
tab? Are the settings on this tab the same for the drop-downs that behave
differently? If the "Show error..." box isn't checked, you'll be able to
enter any value at all.

"Gary" wrote:

Hi everyone:

I have a number of worksheets in my workbook. On my first worksheet, I have
created a drop-down list with options that are specified on another worksheet
in the same book. I did this by creating a list on the other sheet,
highlighting it, and giving it a name in the name box (Let's call it
'namelist'). On the first sheet, I highlighted a range of cells, selected
Data/Validation, and on the Validation criteria I selected List, and for a
source I put =namelist. It works fine. The problem is, sometimes when I
choose from the drop down list on the first worksheet, it only allows me to
choose from the list, no other option is possible. This is what I want. But
sometimes, I can choose from the list or put in any value I want--which I
definitely do not want. For the life of me I can't figure out why some drop
down lists function one way, and some the other. They appear to have
identical criteria and yet this problem continues to occur. Hopefully I've
explained this properly. Any suggestions greatly appreciated. Thank you.

I did try this out but in all cases the 'Show error' box is ticked off, and
the Style is 'Stop'. I'm sure it's something really dumb, but I'm stumped.
But thanks for the suggestion it is the type of thing I probably need to look
at (just not that one).

"bpeltzer" wrote:

Besides the criteria, you have to specify the action Excel will take when the
validation fails. From the Data Validation dialog, check the Error Alert
tab? Are the settings on this tab the same for the drop-downs that behave
differently? If the "Show error..." box isn't checked, you'll be able to
enter any value at all.

If there's a blank cell in the source list range, you'll be able to
enter any value in the cell with the data validation dropdown.

Gary wrote:
Hi everyone:

I have a number of worksheets in my workbook. On my first worksheet, I have
created a drop-down list with options that are specified on another worksheet
in the same book. I did this by creating a list on the other sheet,
highlighting it, and giving it a name in the name box (Let's call it
'namelist'). On the first sheet, I highlighted a range of cells, selected
Data/Validation, and on the Validation criteria I selected List, and for a
source I put =namelist. It works fine. The problem is, sometimes when I
choose from the drop down list on the first worksheet, it only allows me to
choose from the list, no other option is possible. This is what I want. But
sometimes, I can choose from the list or put in any value I want--which I
definitely do not want. For the life of me I can't figure out why some drop
down lists function one way, and some the other. They appear to have
identical criteria and yet this problem continues to occur. Hopefully I've
explained this properly. Any suggestions greatly appreciated. Thank you.




--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html