excel 2003 visual basic intermediate course - Data Validation

Hi Samina

Thank you for your question.

This setting can be used when you are created a dropdown list from a named range, to prevent someone from putting an invalid entry into a cell if there happens to be one or more blank cells in the named range that the list is created from.

Try the following:
Enter 1, 2, 3, 4 into separate, adjacent cells.

Select the cell range where you've entered the numbers above and name the range (I will refer to the named range as 'list' from here on).

Select another blank cell and set data validation as follows:
Allow: List
Source: =list (the named range)
Leave the Ignore blank box ticked.
Click OK.

In the cell you have applied validation to, you should be able to select a number from the dropdown.

Select one of the cells in the named range and delete what's in the cell.

Select the cell you applied validation to - you will see the blank showing in the dropdown list. Because there is a blank you will be able to enter any number into the validated cell now - try it, and you should find that the cell accepts any entry now.

Go back into the data validation settings (Data - Validation) and check the Ignore blank box, then click OK. Now you should find that even though there is a blank in the named range, you won't be able to enter anything that isn't an actual value in the dropdown list.

So in short, this setting just provides you with a little bit of extra protection against invalid entries if you are working with creating a dropdown list from a named range; and for some reason the range has blanks in it.

I hope this helps.
Amanda