Dependent Dropdown using INDEX

Update 12/12/2021

Since writing this post in 2017 about using a method to create a dependent dropdown using INDEX, Excel now has new revolutionary dynamic array formulas that really has overtaken all the workarounds for better dropdown lists we used to do such as described in my post below.  Now the new formula SORT() + UNIQUE() in Excel Office 365 (although not in earlier stand alone Excel versions) is simply the best way of returning a sorted dropdown list.

A quick demo on how…say your list data is in A2:A10 then

1. in say C1 create your list enter  = SORT(UNIQUE(A2:A10)) which gives you a dynamic array list of no blanks in between items and sorted.
2. where you want your dropdown say H1 in the data validation dialog source field enter =C1#

That’s it.  So much easier to implement than it used to be.

But better to place the dropdown list within a Table which is dynamic.  Briefly how – convert the A2:A10 list to a Table (from a cell within this range Control T, My Table has headers unticked) change the column name to ‘MyList’ and change the name of the Table in the ribbon context Menu ‘Table Design’ to MyTable.  Then change the range reference in C1 from A2:A10 to MyTable[Mylist] .  Any new items to your table column list will be included because the dropdown in H1’s validation source field is pointing to a dynamic array list in C1 which in turn has a formula pointing to your new inherently dynamic ‘MyTable’ Table column, ‘MyList’.

For a dependent dropdown as discussed below, you can use SORT() + UNIQUE() + FILTER() for a much simpler solution –  see this nice short and sweet 3 min video from Bill Jelen Mr Excel https://www.youtube.com/watch?v=3eLzIMGpi5g on how.

I am leaving the post below to help people with older Excel versions.

Published Aug 3 2017

Data Validation Dependent Dropdowns are an important practical feature to make your spreadsheets more usable, enable faster more consistent input and robust.   Data Validation Dependent Dropdowns are dropdowns which change according to a another dropdown choice.  For example if I have a data set with say Major Component in column A and Parts in column B and each Major Component might have dozens of Parts, it’s not practical to ask the user to go through the entire list of Parts.   How would they even know if the Part chosen belongs to the Major Component.  Instead we want the dropdown to be smart enough to filter and show only the Parts related to the chosen Major Component.  Also it has to be said that using the Data Validation dropdown feature is already a bit inconvenient as there isn’t any control over the size of the list the user can see.  We could try to use controls like a combo box instead of Data Validation and OK this will allow good control on how it is used and appears but this is only practical where there are only a few needed.  If you need the dropdown on lots of rows that can even be in a table so new rows are dynamic, Data Validation dropdowns are the easiest way.

So anyway, on a job this week I had this Major Component with many many Items scenario in 2 columns within a table.  I was using a Table because ‘once you go tables you never go back’ as the old saying goes.  So other techniques like setting up range names for the parts belonging to each component and then using INDIRECT in the data validation source formula was not going to work, too tedious to set up and would not have been dynamic in if a new component was added down the track.  Could there be a Lookup based solution?

One thing about my Excel approach is I am the eternal optimist.  I wish I could say I was the same in other aspects of life, but I do find I am for Excel work and that is a huge benefit.  Let your Excel mantra be “there has to be a way”.  I tried and tried entering a INDEX based custom format using the Table structured references.  Maybe 2 hours trying this and that.  I thought surely there has to be a way to do this, I really need this in this situation.  Took a break and did a bike ride and after 5 minutes on the bike I thought aaahhh it’s because Data Validation doesn’t like structured references and I need to use range names instead.  I can define the range names using structured references so they are dynamic.  So I peddled hard to get back and try this.

Then I found this gem Stack Overflow post by MVP Tylyn using an INDEX based solution.  I knew this was going to work now and just a matter of perseverance.

So after a bit more mucking it still didn’t work.  Then the penny dropped, my table column for Major components needed to be sorted first (doh).  Then bingo.

My solution was:

1. 1. Sort the data Table on Col A which listed the major components – after this all the Parts related to each major component were together
   2. I set up a 2 x range names MajorComponentsCol and PartsCol defined using their respective Table column as the range name Refers to definition so they would be dynamic
   3. I set up a range name called MajorComponentParts_dd and entered the following formula into the Refers to box and A11 refers to the MajorComponents first row
      

      =INDEX(PartsCol,MATCH(D11,MajorComponentsCol,0)):INDEX(PartsCol,MATCH(D11,MajorComponentsCol,1))

   4. In the data validation custom dialog I chose List then Allow Source: =MajorComponentParts_dd

Basically this formula was looking to the range name PartsCol (Col B in my Table) and defining the start and end points of that part of PartsCol that relates to the Major Component value chosen in D11.  No wonder it needed sorted data.  Important to note this all works because PartsCol has the same number of rows as MajorComponentsCol because both were defined as being columns of the same Table.

Really, really practical way to work with data and make your data validation practical for your users.  I think it’s also easier than other methods for Dependent Dropdowns even in simpler situations with less list items.  The only downside is the column of data that the dropdown is dependent upon (ie in the case above Major Components Col A) has to be sorted.