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

Thanks Debra, I see what your trying to achieve now. It returns an error
within the Data Validation part, however my problem is that there are other
fields within different columns that I wish also to return. If an employee
is on Row 10 and he is the first active employee found, he will move to V2,
however there will be other info that I need on Row 10 (in other columns)
which will now not relate to Row 2. So maybe I didn't explain myself fully.

I guess I need a flter within the Data Validation, or something like that


"Debra Dalgleish" wrote in message
...
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