A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » General Discussion
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Filtering or Formatting DataValidation List



 
 
Thread Tools Display Modes
  #1  
Old May 30th, 2010, 05:20 PM posted to microsoft.public.excel.misc
Lost Cluster
external usenet poster
 
Posts: 11
Default Filtering or Formatting DataValidation List

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.

  #2  
Old May 30th, 2010, 06:46 PM posted to microsoft.public.excel.misc
T. Valko
external usenet poster
 
Posts: 15,759
Default Filtering or Formatting DataValidation List

=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.



  #3  
Old May 30th, 2010, 08:24 PM posted to microsoft.public.excel.misc
Lost Cluster
external usenet poster
 
Posts: 11
Default Filtering or Formatting DataValidation List

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.



.

  #4  
Old May 30th, 2010, 10:11 PM posted to microsoft.public.excel.misc
T. Valko
external usenet poster
 
Posts: 15,759
Default Filtering or Formatting DataValidation List

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.



.



  #5  
Old June 3rd, 2010, 06:02 PM posted to microsoft.public.excel.misc
Lost Cluster
external usenet poster
 
Posts: 11
Default Filtering or Formatting DataValidation List

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(IS NA(MATCH(A$2:A$21,E$2:E$21,0)),ROW(B$2:B$21))),ROW S(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$1 00,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" wrote:

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.



.



.

  #6  
Old June 4th, 2010, 12:10 AM posted to microsoft.public.excel.misc
T. Valko
external usenet poster
 
Posts: 15,759
Default Filtering or Formatting DataValidation List

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?


--
Biff
Microsoft Excel MVP


"Lost Cluster" wrote in message
...
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(IS NA(MATCH(A$2:A$21,E$2:E$21,0)),ROW(B$2:B$21))),ROW S(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$1 00,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" wrote:

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.



.



.



  #7  
Old June 5th, 2010, 10:47 PM posted to microsoft.public.excel.misc
Lost Cluster
external usenet poster
 
Posts: 11
Default Filtering or Formatting DataValidation List

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

"T. Valko" wrote:

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?


--
Biff
Microsoft Excel MVP


"Lost Cluster" wrote in message
...
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(IS NA(MATCH(A$2:A$21,E$2:E$21,0)),ROW(B$2:B$21))),ROW S(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$1 00,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" wrote:

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.



.



.



.

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 02:15 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright 2004-2024 OfficeFrustration.
The comments are property of their posters.