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