Thursday, February 23, 2012

Data Validation Quirk When Using Named Ranges

So here's a goofy item I stumbled upon today. (Others of you, I realize, have known about this for some time.)

Here's the situation:

  1. You are using Data Validation to restrict input for a particular cell to items from a list (Data --} Data Validation --} List).
  2. You have the "Ignore Blanks" option checked.
  3. The list you are referencing is a Named Range.
  4. The list you are referencing contains one or more blank cells.
What happens, you ask? Well, it lets users input any value into the cell that's supposed to be data validated without warning. Nice, right?

Three simple options to fix:
  1. Uncheck the "Ignore Blanks" option.
  2. Don't have any blanks in your Named Range.
  3. Don't use a Named Range.
Just thought I'd pass it along...

Later,

Excel_Geek