Ignore Blanks In Dropdown Not Working...

**nevi** · 06-10-2010, 04:26 AM

Hey There!

This is in Excel 2003, FYI

I having a really frustrating issue with a book I am working on...

In a nut shell, on a tab titled 'Product List' I have a defined name called ProductList ranging from A2:A5017. On a tab titled 'Invoice Input' I have a series of boxes set up with Data Validation to allow a List, the source is =ProductList and both the 'Ignore Blank' and 'In-Cell Dropdown' check boxes are checked off.

Here's the thing; when I go to that drop down list on the Invoice Input tab, all the blanks are there!! I've never had this issue before, and it is driving me mental!!

LOL

A few other details; on the Product List tab, where the ProductList name is being defined, there are a few hundred blank cells towards the bottom. Those cells are truly blank; there is no value and no formula in them. They are not even formatted...

If anyone can help me understand why the blank cells are showing up in my drop down or tell me how to get rid of them I would really appreciate it!

Thanx a million!!

**nevi** · 06-10-2010, 05:06 AM

This is too funny...

I did a little searching around and it turns out I had the exact same problem back in 2006; found the answer in that thread!!

LMAO

When defining the range, I need to enter in this formula:

=OFFSET('Product List'!$A$2,0,0,SUMPRODUCT(--('Product List'!$A$2:$A$5017<>"")),1)

Worked like a charm!!

:D

**azumi** · 11-23-2022, 11:39 PM

I make helper column for data validation, and the formula for dv for exclude blanks is:

=OFFSET(Carribean!$I$2,0,0,SUMPRODUCT(--(Carribean!$I$2:$I$10<>"")),1)

then formula for Departure Port:

=IFERROR(INDEX(Carribean!$E$2:$E$127,MATCH(1,INDEX(($B$5=Carribean!$B$2:$B$127)*($B$6=Carribean!$D$2:$D$127),0,1),0)),"")

For #Nights:

=IFERROR(INDEX(Carribean!$C$2:$C$127,MATCH(1,INDEX(($B$5=Carribean!$B$2:$B$127)*($B$6=Carribean!$D$2:$D$127),0,1),0)),"")

then last for Ports of Call:

=IFERROR(INDEX(Carribean!$G$2:$G$127,MATCH(1,INDEX(($B$5=Carribean!$B$2:$B$127)*($B$6=Carribean!$D$2:$D$127),0,1),0)),"")