Ignore Blank purpose

**pal_slg** · 10-20-2011, 08:09 AM

Hi
can anybody tell the purpose of Ignore Blank Check Box that appear in the Data Validation window? If I check this box I still have the blank cells appearing in the drop down menu.

Is there any other way avoiding the blank cells in the drop down list.
thanks indeed!
Pal

**Marcol** · 10-20-2011, 10:09 AM

See this link Apply data validation to cells

7.To specify how you want to handle blank (null) values, select or clear the Ignore blank check box.
1.
Note If your allowed values are based on a cell range that has a defined name and there is a blank cell anywhere in that range, selecting the Ignore blank check box allows any value to be entered in the validated cell. This is also true for any cells that are referenced by validation formulas: if any referenced cell is blank, selecting the Ignore blank check box allows any value to be entered in the validated cell.

**pal_slg** · 10-21-2011, 02:05 AM

Hi Marcol,

Nice to see you again.
So it means I cannot avoid the blank cells from displaying in the validation list? I am not sure if I got it right.

In fact my problem was - I have a Name Range (A1:A10) and kept few cells blank purposefully. But I do not want those blank cells appear when I use the drop down list to select a value. Those blank cells will not appear unless I put some value in that range. Is it possible?

Thanks again.
pal

**Marcol** · 10-21-2011, 09:09 AM

Why do you need to leave blanks in your named range?
This makes life a bit more complicated than needs be.

However given that you are using 2007 this might help, in pre-2007 dynamic named range "Data_List" will need expanding to substitute IFERROR()

Name:= "Data_List"
Refers to:=

Please Login or Register  to view this content.

Name:= "Validation_List"
Refers to:=

Please Login or Register  to view this content.

In B2 this array formula

Please Login or Register  to view this content.

Confirm with Ctrl+Shift+Enter not just Enter
Drag/Fill Down
Because this formula is volatile, the use of dynamic named ranges reduces the potential impact on your worksheets' performance.

Then say in E1
Data Validation
Allow:+ List
Source:=

Please Login or Register  to view this content.

Hope this helps

**pal_slg** · 10-21-2011, 12:18 PM

Hi Marcol

Thanks a lot for the work. It seems to be complected but solved the problem.
Actually I have several name ranges where the existing data may increase in future. If I do not keep the provision to include the data in the list I have to change the name range every time. Therefore the blank cells may help. Thank you once again!!