Dependent Dropdown using INDEX

If you like this post please share it!
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  

print
Data Validation Dependent Dropdowns are important practical feature to make your spreadsheets more usable 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.  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. 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
Dependent Dropdowns Using Index - Name Mgr Dialog

Dependent Dropdowns Using Index – Name Mgr Dialog

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.

Dependent Dropdown Using INDEX

Dependent Dropdown Using INDEX

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.

 

Tagged with: ,