Display all children for a selection in a Hierarchy


New Member
Apr 3, 2013
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!

Some videos you may like

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.


New Member
Jul 22, 2008
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.

Watch MrExcel Video

Forum statistics

Latest member

This Week's Hot Topics