Ok, let's assume...

A2:A21 = all names
B2:B21 = M is entered to identify those employees that work Monday
C2:Cn = list of employees that work Monday
E2:E15 = drop down lists of those employees that work Monday

As you make selections from the drop downs those employees will be removed
from the list.

Extract the names of those employees that work on Monday. This list will
also be the source for the series of drop down lists in E2:E15.

Array entered** in C2 and copied down to C21:

=INDEX(A:A,SMALL(IF(B$2:B$21="m",IF(ISNA(MATCH(A$2 :A$21,E$2:E$15,0)),ROW(B$2:B$21))),ROWS(C$2:C2)))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

Setup the drop down lists...

Select the range E2:E15
As the source of the lists use:

=IF(AND(ISERROR(C$2:C$21)),NA(),C$2:INDEX(C$2:C$21 ,COUNTIF(C$2:C$21,"*")))

I can post a sample file if you'd like.

--
Biff
Microsoft Excel MVP


"Lost Cluster" wrote in message
...
ColA - LNames (Original list, all my employees)
ColE - Displays only the names of people who work on Monday. Array with
formula =INDEX(LNames,SMALL(IF(ISNA(MATCH(LNames, Monday,0)),
ROW(LNames)),ROWS(E$2:E2))-MIN(ROW(LNames))+1) that you so kindly helped
me
with
I use the counter for the formula in ColG so when I select a name in my
datavalidation drop down list the names disappear as I go down the list.
Pete_UK referred me to a website about Excel Data Validation -- Hide
Previously Used Items in Dropdown and that is where I got the formula for
ColG.

My goal is to create a dropdown list that has only the names of the people
who work on that day and have those names disappear from the list as you
select them so only 1 employee per assignment. As I'm approaching my
goal, I
now have this issue of extra data in my list because I want it to be
expandable. If you have a better plan than what I did, any ideas are
appreciated.
Thanks.


"T. Valko" wrote:

=IF(ROW(FilteredMonday)-ROW(E2)+1COUNT(F2:F100),"",
INDEX(E:E,SMALL(F2:F100,ROW(INDIRECT("1:"&ROWS(Fi lteredMonday))))))
ColF = counter
rows 44-100 are either a 0 or #NUM!.

Isn't that why you have this portion of the formula:

=IF(ROW(FilteredMonday)-ROW(E2)+1COUNT(F2:F100),"",

What is the col F counter for?


--
Biff
Microsoft Excel MVP


"Lost Cluster" wrote in message
...
In ColumnG I have the following formula array from row2 all the way
down
to
row100

=IF(ROW(FilteredMonday)-ROW(E2)+1COUNT(F2:F100),"",
INDEX(E:E,SMALL(F2:F100,ROW(INDIRECT("1:"&ROWS(Fil teredMonday))))))

ColE = FilteredMonday (a list of my employees who work on Mondays)
ColF = counter

In ColumnM I have a drop down list with data validation and the source
for
it is ColumnG. The problem is I only have 43 names but I wanted it to
have
the capacity for 100 names, so rows 44-100 are either a 0 or #NUM!.
How
can
I have the drop down list show only the names without cutting short my
list
in case I do decide to add more names later on?

Thank you.



.