Data Validation Use to Work is No Longer Working

**Charlene Hainsworth** · 05-28-2013, 06:12 PM

I have attached a workbook that I'm having problems with. I have data validation cells on sheet 1 (Cell #'s D43 to D62) that pull from a list on sheet 2 (Cell#'s B28 to B486). My list on sheet 2 is defined under the name manager as Unit_Numbers. When I created my data validation I referenced the list rather than the cell range. I have the error alert selected so my users will know when they have entered invalid data. This has been working for the last 8 years. Today I found out from one of my users that now the data validation allows users to enter any value in cells D43 to D62 regardless of whether they are within my range B28 to B486 on sheet 2. I haven't changed anything that I'm aware of and I don't know how to fix it. My other data validations on the worksheet function properly but not this one. The only thing I thought of that is different is that my computer was updated to Windows 8 from 7 but that shouldn't make a difference because I'm still on the 2010 version of excel which is the same as what I was working with prior to this not working. My workbook is set to automatic calculation and I don't have freeze panes on. (Some of the other ideas I read about that could have been causing the issue) Any ideas?

**XOR LX** · 05-29-2013, 02:24 AM

Hi,

Your problem is that cell B486 (i.e. the last in your Unit_Numbers range) is blank AND you have the Ignore Blank box checked on your data validation:

"The Ignore Blanks option in data validation may not work the way you're thinking; it's very narrow.

If the source list is a named range that contains blank cells, Ignore Blanks allows users to type any entry without receiving an error message. If the source list is a range address (e.g., $A$1:$A$10), validation will block invalid entries whether Ignore Blanks is on or off, and whether the range contains blank cells or not. That's all it does."

(Source:http://answers.microsoft.com/en-us/o...c-ce95b3b5a60a)

You need to either amend your Named Range to exclude blank cells OR uncheck the Ignore Blank box.

Regards