Type in a name and it lists their manager.

ClarissaBlinhova

New Member
Joined
Jul 31, 2017
Messages
4
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. :)
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
make a list of employees and their manager

[TABLE="width: 365"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]name
[/TD]
[TD][/TD]
[TD][/TD]
[TD]list of managers
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]emp 4
[/TD]
[TD]emp 7
[/TD]
[TD][/TD]
[TD]employee name
[/TD]
[TD]manager
[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]emp 1
[/TD]
[TD]emp 7
[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]emp 2
[/TD]
[TD]emp 3
[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]emp 3
[/TD]
[TD]emp 8
[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]emp 4
[/TD]
[TD]emp 7
[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]emp 5
[/TD]
[TD]emp 5
[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]emp 6
[/TD]
[TD]emp 7
[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]emp 7
[/TD]
[TD]emp 9
[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]emp 8
[/TD]
[TD]emp 9
[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]emp 9
[/TD]
[TD]emp 9[/TD]
[/TR]
</tbody>[/TABLE]

In B2 this will lookup the employee in A2

Code:
=INDEX(E3:E11,MATCH(A2,D3:D11,0))
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.



Code:
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,223,227
Messages
6,170,849
Members
452,361
Latest member
d3ad3y3

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top