Multiple (3+) Drop Down List in EXCEL

For complex hierarchical structures with three or more levels create a Multilevel linked list of type parent-Parent. Now structure type: Region-Country-City-Street you can create in MS EXCEL.

article Multilevel linked list in MS EXCEL we've created the multilevel list. But, that list had 2 drawbacks:

  • too complicated formula,
  • maximum number of levels - 3.

the following Proposed solution is devoid of these shortcomings, but the original list containing the names of countries cities and streets is a bit bulky and it needs to be filled according to certain rules.

Decision

as a result, we should be 4-level linked list:

Dropdown lists we will have to form with one large a Named formula

=IF(tablica!D$3=Spyski!$F$5,
OFFSET(Spyski!$D$4,1,0,schetelig(Spyski!$B$5:$B$30,0)),
OFFSET(Spyski!$D$4,poskas(tablica!C10,Spyski!$C$5:$C$30,0),0,schetelig(Spyski!$C$5:$C$30,Tablica!C10)))

to Fill the drop down lists will be out of the table on sheet Lists (see sample file):

For clarity, the levels allocated Conditional formatting.

When completing the tables on the worksheet Lists you want to group the values in the Parent column. Ie if we started to fill in all of the country of America, need to complete this list, then go to the next region. This requirement is necessary for correct operation of the above formula.

Consider how dropdown cell D10 on the sheet table (US cities). The value of the US is in the cell C10.

  • OFFSET() selects from column D (the Descendants) values to create a dropdown based on the value of the cell C10 (USA)
  • the expression MATCH(table!C10,List!$C$5:$C$30,0) returns the position number of the first cell in the column that contains the value USA. This position is the number 10. This position will form our dropdown,
  • now dropdown it remains to specify its length. For this we use the expression schetelig(Spyski!$C$5:$C$30,Tablica!C10).

To enter a formula select a cell D10, call name Manager, and enter formula:br>

the Formula can be stretched to the right and down (or up and to the right): the drop down lists all levels will be generated automatically. The IF() need to generate a list of the top level of the hierarchy - it has no Parent.

to add a new value, for example, a new Country with cities and streets, to add to the table on the sheet Lists the number of lines. Not to fix the formula, the rows should be added by inserting them between already existing rows. 

Now imagine a situation that after filling in the table with drop down lists, was changed the value of the cell C10. For example, instead of the US choose Mexico. Of course, the value in the cell D10 will not change (it remains one of the U.S. cities). To tell the user about the error, use Conditional formatting.

the Formula in rule Conditional formatting is looking for in the table on the worksheet Lists a few Parent-child. If such pair is not found, the cell is highlighted in red fill.

Related articles