Advanced Filter and Data Validation

Hello,

I have a good sized data field 9 colums x 900 rows. I was able to create a data validation drop down that allows me to filter the rows by "Asssign to" Names. I'm running to to the issue where those names are changing or depending on the situations the person who the item is assigned to is no longer with the company. So I want to keep my set names but if there are items assigned to names not on the list I want it to all fall into an other option on the data validation. Is this possible?

This is a mock Table

Name	Student Number	Age	Program	Start Date	Recruiter
Ryan	1	23	Engineering	4/21/14	James G
Sarah	19	24	Pastry Arts	1/27/14	James G
Brenda	3	19	Business Management	2/24/14	Laura K
Lisa	16	21	Digital Graphic Arts	4/21/14	Jaime C
Brent	10	22	Medical Assisting	2/24/14	Adrian H

This is how I would like the Data Drop down to look...

Recruiter

James G

Laura K

Other

So I would want the Recruiters Jamie C and Adrian H to fall under "Others" as shown show above. So when I select others I want it filter out all the data except those that are not on the data validation list.

Im not sure I completely understand your question, but maybe add a helper column that would contain a formula to give "others" if the name is not found?

Again, I suggest you use a helper to ID those names, and include that in your filter

Maybe you could add a "Status" column for the recruiter and use a VLOOKUP table to show recruiters as Active or Unassigned or whatever term you like. If another recruiter is hired and assigned the student, then you can search and replace one recruiter with another and have the status change when you update the VLOOKUP table.