Excel 2003 - Creating Named ranges for a validation list box using a case statement

In the NewPosts worksheet I have the following information
AACleaner
AACleaner2
AACleaner3
BBTeacher1
BBTeacher2
BBTeacher3
CCAssistant1
CCAssistant2
CCAssistant3

And I have 3 categories (Named Ranges) JobFamilyAA, JobFamilyBB and JobFamilyCC

To do the named range I have highlighted a section. Entered the Name range at the top and then pressed enter to commit this range.

In sheet 1 I have the following.

A B C D
Name Post Job_Family_desc NewPost
Sam Smith Cleaner Relief AA
Sara Smith Teaching 3 BB
Jim Brown Care Assistant 1 CC

And I need to create a drop down list for Column D based on the ranges in NewPosts.


So Go to D2. If C2 Job_Family_Desc = “AA” Then use Range JobFamilyAA as validation list
If C2 Job_Family_Desc = “BB” then use Range JobFamilyBB as validation list

If C2 Job_Family_Desc = “CC’ Then use Range JobFamilyCC as validation list

If C2 Job_Family_Desc = “” Then End (Come out or this condition and got to the next bit of the macro)

Then Go to Next one down D3 and repeat until C2 is Empty

Im currently trying lots of things to get this working but if anyone had any tips that would be great

Debbie

Ill definitely be using a macro.

I recorder one that creates a validation for every row so you do one, Check the validation, Set the List box accordingly and move onto the next row. Repeat until you are finished

WorksheetTo_Test_Validation_From_Multiple_Ranges.xls

Pl See the attached file.

Hiya,

Im not sure what you have done but it looks to have worked.

Could you possibly pop in a description of what to do with the info supplied?

Debbie