Data validation, select from list: omit blanks?

[George]

I'm using <data - validation - allow> "list". Which works fine, except
this particular list has blank cells interspersed within it. Is there a
way to have the list ignore the blanks?

TIA,
George

 

[Gary''s Student]

You can create an equivalent list without the blanks.

Say the list is in column C from C1 thru C20.

In B1 enter 1

In B2 enter:
=IF(C2="",-1,1+MAX($B$1:B1))
and copy down

In A1 enter:
=VLOOKUP(LARGE(B:B,ROW()),B$1:C$20,2)
and copy down


Column A will have the same data as column C, but no blanks. Use column A