Use of multi-level dependent lists in MS Excel to map organizational structures

There is a raft of articles on configuration of dependent lists in MS Excel and it is impossible to come up with something brand-new on the subject; however, organizational structures require a particular type of dependent drop-down lists due to the following:

• There can be many levels of departments
• The structure can be frequently changed
• Names of departments can comprise of any characters

In this article, we will demonstrate our hands-on experience how to map positions with the structure of departments using MS Excel Spreadsheets.

Picture 1 illustrates an example of the organizational structure. Divisions form the first level. Departments form the second level and below:

Picture 2 displays the same structure in tabular form. The list is sorted by column “Parent Department” to be further used in the formula:

Picture 3 demonstrates an example of positions being mapped to the organizational structure, where:

• Column “Division level 1” has a fixed dropdown list of divisions from range $A$2:$A$3
• Columns with Departments (from F to L) have dropdown lists evaluated with the following formula like in cell F2:

=OFFSET($A$1;MATCH(E2;$B$2:$B$39;0);0;COUNTIF($B$2:$B$39;E2);1)        

To sum up, a multi-level dependent dropdown lists in MS Excel can be configured with use of one sorted table, “Data Validation” and one formula comprised of functions OFFSET, MATCH and COUNTIF.