data validation: variable drop down

C

CDMAN

How do I make a drop down box of a list in a way that would remove all blank
fields or at the very least move the blank fields to the bottom of the list.
I have a list of names that become visible in the defined list after certain
conditions are met. As the conditions change different names become visable
and others become blank. I want the drop down box for this list to exclude
the blank names.
 
T

T. Valko

You have to remove the blanks from the source of the list. Is the source of
the list generated by formulas?

Need all the details.

Biff
 
C

CDMAN

After much too long trying to figure this out, I got it.
I set up another column that simply listed the row number of the names that
were true, and left blanks for the names that were false. Then I created a
new names list using an array that also moved the blanks to the bottom of the
list.

{=IF(ROW(A2:A11)-ROW(A1)>COUNT(C2:C11),"",INDEX(A:A,SMALL(C2:C11,ROW(INDIRECT("1:"&ROWS(A2:A11))))))}

a2:a11 being the full name list
c2:c11 being the list of rows for true, blanks for false

Now, I added to it's ability by defining the list and adding in the
following to the define.

=OFFSET(Sheet1!$D$2:$D$11,0,0,COUNTA(Sheet1!$D$2:$D$11)-COUNTBLANK(Sheet1!$D$2:$D$11),1)

where d2:d11 is the prior mentioned array

Finish off with a data validation for the defined list and bingo, A changing
drop down with no blanks.
 
Top