Data Validation List controlled by Cell Value

[Andy Roberts]

I have a list of all my emplyees that have worked for me over the last 2
years). Some are still employed and some have left my employment e.g.:-

Employee1 Employed
Employee2 Employed
Employee3 Left
Employee4 Employed
Employee5 Employed

I have a named range set up to include all the Employee names which I then
reference in a data validation list on another sheet. My question is can I
control what appears in that data validation list based on the employment
status in Column B e.g. the list would show...

Employee1
Employee2
Employee4
Employee5

....but if I changed employee3 status to Employed (as they returned to my
compnay to work) then the list would then reflect as ...

Employee1
Employee2
Employee3
Employee4
Employee5

Regards

Andy Roberts
Win XP Pro
Office 2010

 

[lhkittle]

I have a list of all my emplyees that have worked for me over the last 2

years). Some are still employed and some have left my employment e.g.:-



Employee1 Employed

Employee2 Employed

Employee3 Left

Employee4 Employed

Employee5 Employed



I have a named range set up to include all the Employee names which I then

reference in a data validation list on another sheet. My question is can I

control what appears in that data validation list based on the employment

status in Column B e.g. the list would show...



Employee1

Employee2

Employee4

Employee5



...but if I changed employee3 status to Employed (as they returned to my

compnay to work) then the list would then reflect as ...



Employee1

Employee2

Employee3

Employee4

Employee5



Regards



Andy Roberts

Win XP Pro

Office 2010

Give this a try. Seems to describe what you want to do.

http://www.contextures.com/excel-data-validation-add.html#Top

Add New Items to Data Validation Drop Down

In this Excel data validation example, you'll create an Excel Data Validation drop down list that allows users to add new items.

The new data validation items will be automatically added to the drop down list, and the list will be sorted alphabetically.

Regards,
Howard

 

[CellShocked]

I have a list of all my emplyees that have worked for me over the last 2
years). Some are still employed and some have left my employment e.g.:-

Employee1 Employed
Employee2 Employed
Employee3 Left
Employee4 Employed
Employee5 Employed

I have a named range set up to include all the Employee names which I then
reference in a data validation list on another sheet. My question is can I
control what appears in that data validation list based on the employment
status in Column B e.g. the list would show...

Employee1
Employee2
Employee4
Employee5

...but if I changed employee3 status to Employed (as they returned to my
compnay to work) then the list would then reflect as ...

Employee1
Employee2
Employee3
Employee4
Employee5

Regards

Andy Roberts
Win XP Pro
Office 2010

I did this type of thing with conditional formatting.

I use a formula to test a cell value and then 'paint' other cells based
on that value.


http://www.mediafire.com/view/?d7k9dabhw9nb0pw


As opposed to changing the number of values in the list, I show the whole
list and each status.

It would e very easy to poll such a list as yours and make a pivot
table showing only those listed as "employed".

 

[Andy Roberts]

Thanks for the suggestion Howard but its not quite what I need. My list has
items in it that I want to exclude based on the value in the next column to
it - almost like a filter I suppose. This 2nd column I can then use to
change whether entries are included or not in my dropdown list.

 

[Andy Roberts]

Thanks CellShocked but i'm not sure this is what I need. My list has
items in it that I want to exclude based on the value in the next column to
it - almost like a filter I suppose. This 2nd column I can then use to
change whether entries are included or not in my dropdown list.

 

[troy]

I'm trying to figure out how to do this also.

 

[Don Guillett]

I have a list of all my emplyees that have worked for me over the last 2

years). Some are still employed and some have left my employment e.g.:-



Employee1 Employed

Employee2 Employed

Employee3 Left

Employee4 Employed

Employee5 Employed



I have a named range set up to include all the Employee names which I then

reference in a data validation list on another sheet. My question is can I

control what appears in that data validation list based on the employment

status in Column B e.g. the list would show...



Employee1

Employee2

Employee4

Employee5



...but if I changed employee3 status to Employed (as they returned to my

compnay to work) then the list would then reflect as ...



Employee1

Employee2

Employee3

Employee4

Employee5

How about a nice selection event to filter the list to select from