Type in a name and it lists their manager.

[ClarissaBlinhova]

Hi,
I'm trying to set up some function that will allow me to type in a colleague's name and it pulls up his or her manager.
Appreciate any assistance in this.

Thanks you.

 

[Scott T]

make a list of employees and their manager

	A	B	C	D	E
1	name			list of managers
2	emp 4	emp 7		employee name	manager
3				emp 1	emp 7
4				emp 2	emp 3
5				emp 3	emp 8
6				emp 4	emp 7
7				emp 5	emp 5
8				emp 6	emp 7
9				emp 7	emp 9
10				emp 8	emp 9
11				emp 9	emp 9

<tbody>
</tbody>

In B2 this will lookup the employee in A2

=INDEX(E3:E11,MATCH(A2,D3:D11,0))

 

[ClarissaBlinhova]

HI,

Apologies, for being a dum blonde.
I should have explained the following - I have two work sheets the second work sheet is what I have with a list of colleagues along with their manager. he first spread sheet, I want to be able to type in a colleague's name in a box and the box next to it automatically shows that person's manager. And/or if I type in the manager's name that it pulls a list of the manager's staff.

 

[ClarissaBlinhova]

HI,

Apologies, for being a dum blonde.
I should have explained the following - I have two work sheets the second work sheet is what I have with a list of colleagues along with their manager. he first spread sheet, I want to be able to type in a colleague's name in a box and the box next to it automatically shows that person's manager. And/or if I type in the manager's name that it pulls a list of the manager's staff.

 

[Scott T]

You will need VBA to do this. You will need to change the sheet names and ranges to match your data.
Right click on the sheet you want to type in the employee or manager name and select View Code. Past the code in the VBA editor that opens.
The code is for the name to be typed into sheet1 A2 or the Manager to be typed into sheet2 B2 and the list of employees in sheet2 column A and the manager in column B.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim lr As Long
Dim lrm As Long
Dim manlist As Worksheet
Dim mylookup As Worksheet
Set mylookup = Sheets("Sheet1")
Set manlist = Sheets("Sheet2")
lr = mylookup.Cells(Rows.Count, 1).End(xlUp).Row
lrm = manlist.Cells(Rows.Count, 1).End(xlUp).Row
If Not Intersect(Target, Range("A2")) Is Nothing Then
    
    Application.EnableEvents = False
    If lr <= 2 Then lr = 3
    mylookup.Range("B2").ClearContents
    mylookup.Range("A3:B" & lr).ClearContents
        If mylookup.Range("A2") = "" Then
            Application.EnableEvents = True
            Exit Sub
        End If
    mylookup.Range("B2") = Application.Index(manlist.Range("B2:B" & lrm), Application.Match(mylookup.Range("A2"), manlist.Range("A2:A" & lrm), 0))
    Application.EnableEvents = True
    Exit Sub
End If
If Not Intersect(Target, Range("B2")) Is Nothing Then
    Application.EnableEvents = False
    If lr <= 2 Then lr = 3
    mylookup.Range("A2").ClearContents
    mylookup.Range("A3:B" & lr).ClearContents
    For x = 2 To lrm
    lr = mylookup.Cells(Rows.Count, 1).End(xlUp).Row
    If manlist.Cells(x, 2) = mylookup.Range("B2") Then
        mylookup.Cells(lr + 1, 1) = manlist.Cells(x, 1)
    End If
    
    Next x
    Application.EnableEvents = True
End If

End Sub

 

[ClarissaBlinhova]

Thank you, will give try it out. Last resort, could use Access but not sure if my colleagues have access to it or if I have access to VBA. Thanks anyway.