MrExcel.com - Your One Stop for Excel Tips & Solutions

Data validation problem

Posted by Ian G on April 02, 2001 8:41 PM

I am having trouble getting the data validation function in Excel 97 to work properly.

I have used a named range to define the drop down list source and while that part works OK (ie. the drop down list shows the values in the named range) the error alert is not displayed if a user enters an invalid value, and the invalid value is accepted. This happens whether the invalid value is typed directly into the cell or pasted into the cell.

I have the ‘Show error alert after invalid data is entered’ check box selected, and it does this whether I enter my own error message or leave everything at the default settings.

However, when I change the list source from a named range to a range of cells (eg. $G$1:$G$5) the validation function works fine.

I have tried this on a couple of spreadsheets with different data and the same thing happens. I have also been through the data validation troubleshooting topic in Excel help and have looked at all the posts on this site I can find about validation but I can’t spot anything amiss in what I'm doing.

Am I doing something wrong or is this a bug? It’s not a huge problem but I prefer to used ranges where possible and am more than a bit curious as to why it’s happening.

Thanks
Ian

Posted by David Hawley on April 03, 2001 12:58 AM

Hi Ian
The pasting is to be expected as Validation does not stop this. To stop pasting as well you need VBA placed in the Sheet module.

The typing directly into the cell though is a problem. This seems to happen if your named range includes blank cells, which I assume you have should you need to add to the list at a later date.

You can get around this by creating a dynamic named range. If you are not familiar with these you can see some examples on my Website under the link "Dynamic Ranges"


Dave
OzGrid Business Applications

Posted by Ian G on April 03, 2001 5:17 PM

Dave
Many thanks. You were right - it's because I was using blank cells in the range. I have used option 4 from your website page (lots of very helpful stuff there!) on dynamic ranges. Although learning how to create dynamic ranges is a much more significant discovery to me than solving the validation thing, using this method didn't fix the immediate problem. So I had another look at what I was doing and realised that if I deselect the 'ignore blanks' check box it works fine with named ranges.
Regards
Ian