Filters on Data Validation

[John]

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

 

[Norman Jones]

Hi John,

See Debra Dalgleish's tutorial on dependent lists:

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

 

[John]

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

 

[Debra Dalgleish]

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]

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]

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]

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