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:
- Sort the data Table on Col A which listed the major components – after this all the Parts related to each major component were together
- 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
- 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
- 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.