extract and display hierarchical structure from source data in spreadsheet

I hope someone can help me with this task. I have a spreadsheet detailing staff’s details, e.g. their name, job title, cost centre code, team name, line manager info etc. There are over 2000 employees in the division. The division’s hierarchy is defined through post numbers, e.g. post number 005 reports to post number 001. This means whoever has been appointed to post number 005 reports to whoever occupies post number 001.

Now I've been asked to explore the possibilities of having a drill down list to show the hierarchical structure of the entire division. To demonstrate this I am attaching a sample spreadsheet. What we want is to have something like this: AA Milne needs to be automatically listed without the need of selecting him. If AA Milne is selected then the list should be able to expand to the next hierarchical level which is Christopher Robin but no further than that. If we continue selecting Christopher Robin, Winnie-the-Pooh and Owl's details should appear and so on...What we don't want is to have everything automatically displayed in one go..

Is this something possible to do in Excel? Any suggestions will be gratefully received.

Thank you very much for your hellp in advance

Maggie

Personally, I am not understanding what you mean by ... automatically listed without selecting, or ... selected then the list should be able to expand to the next hierarchy.

Hi Bob,

Thank you for attempting to understand my clumpsy language. I did find it a bit struggle to explain what I was hoping to achieve. Essentially what I would like to do is to translate the data in that spreadsheet to something like the chart attached. Ideally the user can expand or collapse the chart. Can this be done in Excel?

Thank you

Maggie

Hi Maggie,

Its not possible without VB scripting.   Do you have Visio?  If so, that will do what you are looking for.  I've linked to a sample that uses the xls file you provided.

Eli

I think what is required here is a Pivot table. Once created there are options in the Pivot table to drill to details.