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,

Oh yeah, and aphebatical would be nice too.
Thank,

"I''m Still Here" wrote:

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,

Why not treat the list as a "database" using the "Form" method of data entry
and deletion. I believe this will give you the desired result.

HTH John

How do I do that?

"John Eppley" wrote:

Why not treat the list as a "database" using the "Form" method of data entry
and deletion. I believe this will give you the desired result.

HTH John

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,

Gave it a shot. It is too hard for me. I'm willing to manually alphebatize,
and make sure there are no blanks in between entries. Call you tell me how to
eliminate the "Nil"s that appear after the last entry?

Thanks,

"Biff" wrote:

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,