Display all children for a selection in a Hierarchy

[dj1ce]

Hello Everyone

I have a list of employees and their managers in excel on Sheet1 (A2 through A? is the employee and B2 through B? is their manager). This goes (so far) up to 8 levels deep. I then have a data validation list that sources column A on sheet1 (The list of employees) that is located in cell B2 on sheet2. I want to be able to select a person in cell B2 and have everyone that works for them (directly and indirectly) display in cells C4 through C?. Here is my 'for instance' example below:

John works for Fred. Fred works for Sally. Sally works for Bob. Jim works for Sally.

If I select John, I should see no one
If I select Jim, I should see no one
If I select Fred, I should see John
If I select Sally, I should see Fred, Jim, and John
If I select Bob, I should see Sally, Fred, Jim, and John

Has anyone seen anything like this before? I am not new to VBA, so I can tweak examples if needed, but I can't even think of how to start coding this. Also, I have looked up hierarchy, parent-child, and tree and could not find anything. I did find something in access, but I need this for excel 2007 or 2010. Thanks for your help!

 

[shg]

See the workbook at https://www.box.com/s/lw3lfk71kbc121ac768o

On the Via Code tab, after generating the table, you can autofilter the Reporting Chain column for Contains, and enter an employee number to see their organization.

 

[thedevil]

See the workbook at https://www.box.com/s/lw3lfk71kbc121ac768o

On the Via Code tab, after generating the table, you can autofilter the Reporting Chain column for Contains, and enter an employee number to see their organization.

Not aiming to revive a long dead thread but this document is wonderful. Is it possible to expand this to include multiple top level nodes? I have a data set with more than 4 tree sets together and separating them is challenging.