lookup function for reporting structure

[ettenna]

first - let me say you folks who help here are AMAZING!!!! and I would not make it without your assistance!!!! I am a newbie when it comes to excel, so this may not even be possible.

I have a sample tab - what I am looking for is a formula that will help me locate anyone who reports to and under someone's ID

sample - EEID 42 has 10 direct reports but under EEID 231 has 1 direct report and same with EEID 395. If I add my top employee worksheet I would like to find any employees who report under them.

I hope my request is making sense - please let me know if this is possible or if I'm searching for unicorns?

thank you in advance!!!

[MarvinP]

Hi ettenna,

I did a Pivot Table using your second sheet. Does my answer give you what you need? See the attached.
Supervisor Report.xlsx
It looks like you want a new sheet for each employee?? What do you want the answer to look like?

[ettenna]

thank you Marvin, it does not - I did try that first but once I add names and titles to the sheet it throws them all of.

[ettenna]

I will make a new tab for each upper manager - I'm looking to add for columns with employee information which is why the pivot table won't work.

[kvsrinivasamurthy]

Pl show the expected result.

[ettenna]

what is array formula?

[Glenn Kennedy]

You don't need to worry about array formulas. they no longer exist in O365... or at least they don't need special treatment. In any event, it's just part of his signature. the important bit was his request for a sample file with some expected results!!

[ettenna]

thank you Glen - There is a sample tab

[JeteMc]

This proposal employs two helper columns (D:E) on the Reports To sheet.
The helper columns may be moved and/or hidden for aesthetic purposes and are populated as follows:
For column D:

Please Login or Register  to view this content.

For column E:

Please Login or Register  to view this content.

Assuming that the first cell in the EEID column on the Summary sheet will be manually filled with the supervisor of inquiry:
The rest of that column is populated using:

Please Login or Register  to view this content.

The Supervisor EID column is populated using:

Please Login or Register  to view this content.

The Employee # column is populated using:

Please Login or Register  to view this content.

Let us know if you have any questions.