Extracting a hierarchy from a worksheet

[Jamo1985]

Hi,

I've got a list of employees but what I want to be able to do is to be able to enter an employee number and for the results to bring back a list of everyone who falls under them in the company hierarchy.

Emp No	First Name	Last Name	Manager No
1	A	Smith
2	B	Jones	1
3	C	Harris	1
4	D	Yates	2
5	E	Fowler	2
6	F	Thomas	4
7	G	Hunt	4
8	H	Cooper	5
9	I	White	8
10	J	King	7

I've tried to display what I've got to work with, which is a pretty basic layout - 4 columns: employee number, first name, surname and their manager's employee number. I want some set-up to allow a user to enter an employee number so if say number 1 was selected then everything would be brought back because all employees report into number 1, or report into somebody who reports into number 1 (even if there are a few layers in the hierarchy). If somebody entered number 7, then it would show that only 1 person sat below them in the hierarchy.

Does anybody have any ideas of how I could go about creating this?

[rylo]

Hi

Try this to see if it nominates the expected results.

F1: Enter the required number (eg 7)
E2: =IF(OR(A2=$F$1,D2=$F$1),"yes","no")
E3: =IF(OR(A3=$F$1,D3=$F$1),"yes",IF(MAX(($A$2:A2=D3)*($E$2:E2="yes")),"yes","no")) Note this formula is array entered (CTRL, SHIFT, ENTER. Copy from E3 down to E11.

It should bring back a yes for all the entries that are related to the emp no entered in F1. If this is right, then you could put in a heading in E1, and use an autofilter to return the yes entries.

See how it goes.

838447.xlsx

rylo