Filtering or Formatting DataValidation List

[Lost Cluster]

In ColumnG I have the following formula array from row2 all the way down to
row100

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

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.

 

[T. Valko]

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

INDEX(E:E,SMALL(F2:F100,ROW(INDIRECT("1:"&ROWS(FilteredMonday))))))
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)+1>COUNT(F2:F100),"",

What is the col F counter for?

 

[Lost Cluster]

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]

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.

 

[Lost Cluster]

Hello Valko,

The formula for the source in Col E for Data Validation does not seem to
filter the Monday only employees. At the moment it lists the first 4 people
from my C Column but then as you select them it starts showing the other
people who work on Monday one at a time and it also list the people who don’t
work on Monday so it lists everyone but not at the same time. It also
displays the #NUM! error, which was my original problem. I tried increasing
the range to 100 instead of 21 but there was no change. I also modified the
formula for Col C because I have my days off listed in a string of text. So
at the moment Col C is showing the people who are off on Monday. Maybe we
would modify the formula for C to not show the names that have an M in Col B.
I tried the following syntax for col C but didn’t work I got a Value error
=INDEX(A:A,SMALL(IF(ISNUMBER(SEARCH("m",B2))=TRUE,"
",IF(ISNA(MATCH(A$2:A$21,E$2:E$21,0)),ROW(B$2:B$21))),ROWS(C$2:C2)))

Here is what my formulas look like after I modified them a bit.

Col A all my employees
Alejandro
Aros (ICE)
Avila
Baker
Balicki
Bay (ICE)
Beghin
Boltz
Bowman (ICE)
Bratcher (ICE)
Brito (ICE)

Col B (Days off)
m
Sa/Su/M-W
F/Sa/Su-Tu
M/Tu/W-F
Tu/W/Th-Sa
F/Sa/Su-Tu
Su/M/Tu-Th
Tu/W/Th-Sa
Th/F/Sa-M
Su/M/Tu-Th
M/Tu/W-F


Col C –
=INDEX(A:A,SMALL(IF(ISNUMBER(SEARCH("m",B2)),IF(ISNA(MATCH(A$2:A$21,E$2:E$21,0)),ROW(B$2:B$21))),ROWS(C$2:C2)))

Filtered employess who have Monday off
Alejandro
Aros (ICE)
#NUM!
Baker
#NUM!
#NUM!
Beghin
#NUM!
Bowman (ICE)
Bratcher (ICE)
Brito (ICE)
#NUM!
#NUM!
#NUM!
#NUM!
#NUM!
#NUM!
#NUM!
#NUM!
#NUM!

Col E
=IF(AND(ISERROR(C$2:C$100)),NA(),C$2:INDEX(C$2:C$100,COUNTIF(C$2:C$100,"*")))
Under my drop down this is all it shows for the first cell E2 it doesn’t
even give me the scroll down bar. After I select 2 names then it starts
listing other employees who don’t belong in that list like “Balicki†refer to
the list in Col C

Cell E2
Alejandro
Aros (ICE)
#NUM!
Baker
#NUM!
#NUM!
Beghin


Cell E4
Alejandro
Aros (ICE)
#NUM!
Balicki
#NUM!

 

[T. Valko]

Sa/Su/M-W
F/Sa/Su-Tu
M/Tu/W-F
Tu/W/Th-Sa
F/Sa/Su-Tu
Su/M/Tu-Th
Tu/W/Th-Sa
Th/F/Sa-M
Su/M/Tu-Th
M/Tu/W-F

I'm not sure how to read that. But, if you're using an ISNUMBER(SEARCH for
"M" then these are the entries that should be matched:

Sa/Su/M-W = matched
F/Sa/Su-Tu
M/Tu/W-F = matched
Tu/W/Th-Sa
F/Sa/Su-Tu
Su/M/Tu-Th = matched
Tu/W/Th-Sa
Th/F/Sa-M = matched
Su/M/Tu-Th = matched
M/Tu/W-F = matched

Do you want me to post a sample file?

 

[Lost Cluster]

would you please post that sample file. i havent been able to get it to work
for me. thanks.