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". 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.
Reply With Quote
Bookmarks