[ker_01]

I have a worksheet that includes a /lot/ of data validation cells.

There is one particular range of data validation cells (A29:A38) where we
have identified a problem. We have not yet determined if this same problem
will affect the rest of the workbook, or our overall project.

The target range cells have data validation enabled:
Allow: List
Source: =Positions (a named range from a hidden sheet)
Ignore blank and In-cell dropdown are both checked.
There is no input message
Error Alert: Stop; and "Show error alert after invalid data" is checked.

When the cell is selected, the dropdown arrow appears, and the list contains
the expected entries, and they can be selected as expected.

However, what we just found out is that users can type (not copy or
autofill, really type) a non-list item into these cells and it doesn't
trigger the error alert or prevent the entry. So, now we are getting invalid
data trickling back into our data aggregation tables.

I tried the instructions to "Find all cells with data validation" at
http://office.microsoft.com/en-us/ex...CH100648501033
Using both 'Go to special' all and same, and it appears to be highlighting
the correct cells (which is most of the worksheet for 'all')

So that verifies that Excel thinks these cells have data validation, but
leaves me with no other clues as to why the data validation isn't working. I
can type random gibberish into those data validation cells, and it never
throws an error.

Any ideas on what else to check?

Thank you,
Keith

[ker_01]

Found the answer at: http://www.contextures.on.ca/xlDataVal08.html#Freeze

Looks like "Ignore blanks" doesn't at all mean what I thought it meant.

Keith

"ker_01" wrote:


I have a worksheet that includes a /lot/ of data validation cells.

There is one particular range of data validation cells (A29:A38) where we
have identified a problem. We have not yet determined if this same problem
will affect the rest of the workbook, or our overall project.

The target range cells have data validation enabled:
Allow: List
Source: =Positions (a named range from a hidden sheet)
Ignore blank and In-cell dropdown are both checked.
There is no input message
Error Alert: Stop; and "Show error alert after invalid data" is checked.

When the cell is selected, the dropdown arrow appears, and the list contains
the expected entries, and they can be selected as expected.

However, what we just found out is that users can type (not copy or
autofill, really type) a non-list item into these cells and it doesn't
trigger the error alert or prevent the entry. So, now we are getting invalid
data trickling back into our data aggregation tables.

I tried the instructions to "Find all cells with data validation" at
http://office.microsoft.com/en-us/ex...CH100648501033
Using both 'Go to special' all and same, and it appears to be highlighting
the correct cells (which is most of the worksheet for 'all')

So that verifies that Excel thinks these cells have data validation, but
leaves me with no other clues as to why the data validation isn't working. I
can type random gibberish into those data validation cells, and it never
throws an error.

Any ideas on what else to check?

Thank you,
Keith