Excel: Discard empty cell values in a drop down list (named range)

[inddon]

Hello There,

I have 3 named ranges
1. company code ( 5 values- from rows A1 to A5)
2. Customer Name ( 10 values from B1 to B10
3. Country Name (15 values from C1 to C15)

Created 3 names ranges from A1 to A20, B1 to B20 and C1 to C20 for the above respectively. Also, created data validation (DV) on 3 cells for the above with 'List' with their given name range. In DV, settings ignore blank is checked.

When I use the drop down list for Company code, it gives me extra 15 empty values, and the same goes for the other 2 list as well 10 and 5 empty values respectively.

Even though in DV, ignore blanks is checked why is it showing blank values in the list.

Could you please advice on how to resolve this issue?

Thanks & regards
Don

 

[Somendra Misra]

Hi Don,

Instead of creating direct ref. range in the the named range, create dynamic range. Like the one below.

For A1:A5: in ref box of name manager give below formula.
=$A$2:INDEX($A$2:$A$30,match(TRUE,ISBLANK($A$2:$A$30),0)-1)

Like wise for other three columns also.

Regards,

 

[inddon]

Hi Don,

Instead of creating direct ref. range in the the named range, create dynamic range. Like the one below.

For A1:A5: in ref box of name manager give below formula.
=$A$2:INDEX($A$2:$A$30,match(TRUE,ISBLANK($A$2:$A$30),0)-1)

Like wise for other three columns also.

Regards,

Hello Somendra,

Thank you for you help. It works fine

Regards
Don