combining tasks

[SusanL]

As the last part of a project I need to create a schedule. I have found several useful tools and examples from http://www.contextures.com/excelfiles.html#DataVal

All of which use data validation to create a drop down list. I was hoping to combine several of the methods and perhaps get them to all execute as one. I understand this may be impossible and appreciate any attempts to solve.

What I would like to do is this: I have a schedule structure set up on sheet 1. On sheet2 I have set up lists in columns A, B and C. In A, it lists all the shift1 employees A1:A50. In B, it lists the Shift 2 Employees B1:B50, and so on for C. On my schedule in sheet1 what I would like to do is when I click on a certain cell, a dropdown arrow appears and lets me select a 1, 2 or 3 (indicating the shift). Based on what I select here, when I click on the adjacent cell to the left a list will appear that correspond to the choice I made and only show the appropriate list of names from the list on sheet2. It would then not allow you to use that name again on this schedule. Lastly, If I enter a name not on the list it will add that name to the list and alphabetize the list.

Please help!
Suzy