data validation: variable drop down
 

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.

data validation: variable drop down
 

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

"CDMAN" wrote in message
...
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.

data validation: variable drop down
 

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,RO W(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.



"T. Valko" wrote:

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

"CDMAN" wrote in message
...
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.