Data Validation List That Ignores The Blank Cells

Hey There,

I'm having a problem with a data validation list. The ammount of entries in this list will fluctuate week over week, so my range from the list needs to be O2 to O300. Those cells are populated by another formula.

I go to Data - Validation, select List, input the cell range and make sure the "Ignore Blanks" check box is marked, but it is still adding all of the blanks into the drop down box it creates.

Does anyone know a way to get it to filter out those blanks?

Thanx a million in advance!!


Nevi

Hey There,

Thanx for the reply!! I still can't get it working though. I did a google search using what you gave me and found some more info, but I can't seem to get it pieced together...

http://www.pcreview.co.uk/forums/thread-3475779.php

I go to Insert - Name - Define, I am setting the name as List, and in refers to I am entering:

=OFFSET($K$1,0,0,SUMPRODUCT(--($K$1:$K$1000<>""""),1))

When I go to Data - Validation, and try to set the drop down list to =List it is giving me the error "The Source currently evaluates to an error. Do you with to continue?" and it doesn;t work.

Thanx!!


Nevi

Small update; I posted the formula wrong by mistake. The formula that I am defining the Name with is:

=OFFSET(Detailed!$K$1,0,0,SUMPRODUCT(--(Detailed!$K$1:$K$1000<>"")),1)

I should also maybe mention that the list is now in column K instead O, just to avoid any confusion.

Thanx a million again for the help. I really appreciate it!!


Nevi

OMG... I am an idiot...

It is alway the stupid little things that mess us up. I forgot that I didn't spell out "Detailed" as the name of the tab; it's abreviated it Dtld.

=OFFSET(Dtld!$K$1,0,0,SUMPRODUCT(--(Dtld!$K$1:$K$1000<>"")),1)

The above worked like a chanrm.

Thanx!!!!


Nevi