Hi!

This will take a lot of work. Are you sure you're up to it? Afterall, "It's
too troublesome to manually make sure there are no gaps if someone in the
middle of the list leaves." <vbg

Assuming the names are in column A, A1:An.

Create these named formulas:

Name: Names

Refers to:

=OFFSET(Sheet1!$A$1,,,LOOKUP(REPT("z",255),Sheet1! $A$1:$A$100,ROW(Sheet1!$A$1:$A$100)))

Name: List

Refers to:

=OFFSET(Sheet1!$B$1,,,SUMPRODUCT(--(LEN(Sheet1!$B$1:$B$100)0)))

In the above formulas, I'm using an arbitrary range size of 1:100. You'll
have to adapt to suit.

In cell B1 enter this formula as an array using the key combo of
CTRL,SHIFT,ENTER:

=IF(ROWS($1:1)<=COUNTA(Names),INDEX(Names,MATCH(SM ALL(IF(Names<"",COUNTIF(Names,"<"&Names)),ROWS($1 :1)),IF(Names<"",COUNTIF(Names,"<"&Names)),0)),"" )

You'll have to copy down to enough rows to allow for future expansion in
that all newly added names will be "captured" in this new list. For example,
you now have a total of 20 names in column A so copy this formula to 40 or
50 rows.

This will list the names alphabetically.

Setup the drop down list in cell C1.

As the source for the list use this:

Source: =List

Biff

"I''m Still Here" wrote in message
...
Column A has a list of employees. As employees come and go, this list will
change. In C1, I need a dropdown list that will list all employees. I'm
currently using this: =A:A in the List option of Data Validation. It
works,
but it it gives me "Nil" for every cell that is blank. It's too
troublesome
to manually make sure there are no gaps if someone in the middle of the
list
leaves. Additionally, every cell below the last entry also appears in the
dropdown list as "Nil" and since I choose the whole column, there are a
lot
of them. It's ok to use B as a helper column if necessary. I want A to be
able to have blanks in the middle , but the drop down list in C1 to not
include Nil anywhere. By the way, the check box for Ignore Blank has no
effect.

Thanks,