I wish to use a Data Validation drop down list to pick Employees from a
Named Range, but only those that do not have a leaving date within. My data
is dynamic. Say my employees are in Column S and Leaving date (blank if
still working) is in Column M.

I have it set up currently, but all my employees are listed, I only want
those that are still working to be listed. I guess its some some of filter
but not sure how its done

Thanks

Hi John,

See Debra Dalgleish's tutorial on dependent lists:

http://www.contextures.com/xlDataVal02.html


---
Regards,
Norman



"John" wrote in message
...
I wish to use a Data Validation drop down list to pick Employees from a
Named Range, but only those that do not have a leaving date within. My
data
is dynamic. Say my employees are in Column S and Leaving date (blank if
still working) is in Column M.

I have it set up currently, but all my employees are listed, I only want
those that are still working to be listed. I guess its some some of filter
but not sure how its done

Thanks

Thanks Norman

But I don't have two separate list for ex and current employees in that its
defined by way of whats in the same column i.e. if there is no date in
Column M then they are current employees, if there is a date in Column M
then they are not, how could I setup two ranges within the same column


"Norman Jones" wrote in message
...
Hi John,

See Debra Dalgleish's tutorial on dependent lists:

http://www.contextures.com/xlDataVal02.html


---
Regards,
Norman



"John" wrote in message
...
I wish to use a Data Validation drop down list to pick Employees from a
Named Range, but only those that do not have a leaving date within. My
data
is dynamic. Say my employees are in Column S and Leaving date (blank if
still working) is in Column M.

I have it set up currently, but all my employees are listed, I only want
those that are still working to be listed. I guess its some some of
filter
but not sure how its done

Thanks

If your first name is in cell S2, enter the following formula in T2:


=IF(ISERROR(INDEX(S:S,MATCH(ROW()-1,T:T,0))),0,INDEX(S:S,MATCH(ROW()-1,T:T,0)))

Copy down to the last row of data

In cell V2, enter:

=INDEX(S:S,MATCH(ROW()-1,T:T,0))

And copy down to the last row of data.

Define a name for the list, e.g.: NameList
Refers to:
=OFFSET(Sheet1!V2,0,0,COUNTA(Sheet1!$V:$V)-COUNT(Sheet1!$V:$V),1)

In the data validation dialog box, use NameList as the List source.

John wrote:
Thanks Norman

But I don't have two separate list for ex and current employees in that its
defined by way of whats in the same column i.e. if there is no date in
Column M then they are current employees, if there is a date in Column M
then they are not, how could I setup two ranges within the same column


"Norman Jones" wrote in message
...

Hi John,

See Debra Dalgleish's tutorial on dependent lists:

http://www.contextures.com/xlDataVal02.html


---
Regards,
Norman



"John" wrote in message
...

I wish to use a Data Validation drop down list to pick Employees from a
Named Range, but only those that do not have a leaving date within. My
data
is dynamic. Say my employees are in Column S and Leaving date (blank if
still working) is in Column M.

I have it set up currently, but all my employees are listed, I only want
those that are still working to be listed. I guess its some some of

filter

but not sure how its done

Thanks








--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

Thanks for your reply

Not really following your logic here. How does the employee name (in Col S)
relate to whether they are active or retired employees (indicated via a
termination date in Col M)?


"Debra Dalgleish" wrote in message
...
If your first name is in cell S2, enter the following formula in T2:



=IF(ISERROR(INDEX(S:S,MATCH(ROW()-1,T:T,0))),0,INDEX(S:S,MATCH(ROW()-1,T:T,0
)))

Copy down to the last row of data

In cell V2, enter:

=INDEX(S:S,MATCH(ROW()-1,T:T,0))

And copy down to the last row of data.

Define a name for the list, e.g.: NameList
Refers to:
=OFFSET(Sheet1!V2,0,0,COUNTA(Sheet1!$V:$V)-COUNT(Sheet1!$V:$V),1)

In the data validation dialog box, use NameList as the List source.

John wrote:
Thanks Norman

But I don't have two separate list for ex and current employees in that
its
defined by way of whats in the same column i.e. if there is no date in
Column M then they are current employees, if there is a date in Column M
then they are not, how could I setup two ranges within the same column


"Norman Jones" wrote in message
...

Hi John,

See Debra Dalgleish's tutorial on dependent lists:

http://www.contextures.com/xlDataVal02.html


---
Regards,
Norman



"John" wrote in message
...

I wish to use a Data Validation drop down list to pick Employees from a
Named Range, but only those that do not have a leaving date within. My
data
is dynamic. Say my employees are in Column S and Leaving date (blank if
still working) is in Column M.

I have it set up currently, but all my employees are listed, I only
want
those that are still working to be listed. I guess its some some of

filter

but not sure how its done

Thanks








--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

Sorry, I copied and pasted the wrong formulas for T2 and V2.

In cell T2, the formula should be:

=IF(M2="",MAX($T$1:T1)+1,"")

In cell V2, the formula should be:


=IF(ISERROR(INDEX(S:S,MATCH(ROW()-1,T:T,0))),0,INDEX(S:S,MATCH(ROW()-1,T:T,0)))

John wrote:
Thanks for your reply

Not really following your logic here. How does the employee name (in Col S)
relate to whether they are active or retired employees (indicated via a
termination date in Col M)?


"Debra Dalgleish" wrote in message
...

If your first name is in cell S2, enter the following formula in T2:




=IF(ISERROR(INDEX(S:S,MATCH(ROW()-1,T:T,0))),0,INDEX(S:S,MATCH(ROW()-1,T:T,0
)))

Copy down to the last row of data

In cell V2, enter:

=INDEX(S:S,MATCH(ROW()-1,T:T,0))

And copy down to the last row of data.

Define a name for the list, e.g.: NameList
Refers to:
=OFFSET(Sheet1!V2,0,0,COUNTA(Sheet1!$V:$V)-COUNT(Sheet1!$V:$V),1)

In the data validation dialog box, use NameList as the List source.

John wrote:

Thanks Norman

But I don't have two separate list for ex and current employees in that

its

defined by way of whats in the same column i.e. if there is no date in
Column M then they are current employees, if there is a date in Column M
then they are not, how could I setup two ranges within the same column


"Norman Jones" wrote in message
...


Hi John,

See Debra Dalgleish's tutorial on dependent lists:

http://www.contextures.com/xlDataVal02.html


---
Regards,
Norman



"John" wrote in message
...


I wish to use a Data Validation drop down list to pick Employees from a
Named Range, but only those that do not have a leaving date within. My
data
is dynamic. Say my employees are in Column S and Leaving date (blank if
still working) is in Column M.

I have it set up currently, but all my employees are listed, I only

want

those that are still working to be listed. I guess its some some of

filter


but not sure how its done

Thanks






--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html






--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html