Return a list (Column) using data validation or list box

[Claymationator]

I am in a predicament and was wondering if anyone could help me out. I am working on some reports at work where I have a list of everyone in my department and who their manager is in 2 separate sorted columns. What I want to be able to do is set up a cell on one sheet that has the manager name in a data validation list, and depending on the manager that is selected it would return the complete list of agents that are under that manager.

I do know that I can set it up so that it will return one agent name at a time, or a validated list of just those agents on his/her team in once cell, but I want to know if it is possible to return the complete list of agents with just one cell selected.

Any help would be appreciated. Thanks!

 

[Joe Was]

Check here this is asked often:


http://www.mrexcel.com/board2/viewtopic.php?t=99354

 

[Claymationator]

Thanks for the link, unfortunately that isn't all that I am looking to do. I can get that far in making a dependent dropdown list, but I want to be able to return the list by only selecting the one manager cell instead of then having to go to the other dependent cells and changing each one.

Maybe I can explain a little more clearly. I have in cell B2 a validation list that has the names of the managers within the department, and then in column C from C2:C31 I have the agent column. What I want it to do is be able to select the manager name in B2 and then have the cells automatically filled in from C2:C31 with all of the agent names that correspond with that specific manager.

Does that explain it better?

 

[Joe Was]

If your managers are in say column "A" and the employees are in column "B" where the employees manager is on the same row. This code will automatically check the list for new additions. If the sheet that gets the employee list is triggered by a Validation Dropdown in Cell A1 on any sheet then that sheets event code will pull the correct employees for that manager from the sheet that has that master list.


Private Sub Worksheet_Change(ByVal Target As Range)
'Sheet module code, like: Sheet3.
Dim myLstRng As Range
Dim myBotLst&


'On Sheet3, cell A1 is the Validation dropdown of managers!
If Target.Address <> "$A$1" Then Exit Sub

'This is the column on Sheet3 that gets the Employee list results!
If Target.Value <> "" Then
Columns("B:B").Delete

'Last row of Manager-Employee list on Sheet4.
myBotLst = Sheets("Sheet4").Range("A65536").End(xlUp).Row

'Manager lists current range, on Sheet4!
Set myLstRng = Sheets("Sheet4").Range("A1:A" & myBotLst)

For Each Cell In myLstRng
'Find this managers employees and copy them to Sheet3!
If Target.Value = Cell.Value Then
Sheets("Sheet3").Range("B65536").End(xlUp).Offset(1, 0).Value = Cell.Offset(0, 1).Value
n = n + 1
End If
Next Cell

'Error!
If n = 0 Then MsgBox "No employees found, for: " & Target.Value

End If
End Sub



It is also easy if you name each managers list of employees and store them somewhere, I label the column with the manager and use the rows for the employees. Then I write code to erase the current range that gets the list, then use the Select Case structure to look for a managers name change in the Validation Dropdown Box and then return the correct list to the display range.


Private Sub Worksheet_Change(ByVal Target As Range)
'Sheet module code, like: Sheet1.

'On Sheet1, cell A1 is the Validation dropdown of managers!
If Target.Address <> "$A$1" Then Exit Sub

'This is the column on Sheet1 that gets the Employee list!
If Target.Value <> "" Then
Columns("B:B").Delete

Select Case Target.Value

'These are the range names on Sheet2 for the list of employees for these managers!
Case "Boss_One_Name"
Sheets("Sheet2").Range("Boss_One_Name").Copy Destination:=Sheets("Sheet1").Range("B1")

Case "Boss_Two_Name"
Sheets("Sheet2").Range("Boss_Two_Name").Copy Destination:=Sheets("Sheet1").Range("B1")


Case Else ' Other values.
MsgBox "No employees found, for: " & Target.Value
End Select
End If

End Sub

 

[Claymationator]

Thanks for the reply. I was able to find a solution to it without having to write a macro, but I will have to give your solution a try and see if one option works better than another.

I appreciate the help![/img]