This Excel Factor tip was sent in by Roberto Mensa of Italy.
Words by Mynda Treacy.
But, Roberto’s solution is better because it’s dynamic, and that means you can change the names of the lists, or add new items to the lists and it will automatically include them in the data validation list like this:
Let’s take a look at how Roberto uses some clever Named Formulas with data validation to dynamically generate the lists.
How to set up Dynamic Dependent Data Validation Lists
The lists are on a sheet called tbl_data in cells B2:E7. Row 2 contains the categories. This is the primary list (it will be called ‘Categories’) and rows 3:7 contain the secondary lists (they will be called ‘Choice’).
First define a Named Formula for the Categories. To do this go to the Formulas tab of the ribbon and select ‘Define Name’.
Name it ‘Categories’ with the scope ‘Workbook’ and in the ‘Refers to:’ field enter this formula:
This creates a reference to the column headers for the primary list. i.e. Fruit, Vegetable, Cheese, Meat.
The INDEX and COUNTA functions in the formula count how many cells contain data in row 2, whereby generating a dynamic range for the named formula. The +1 on the end is to account for the blank cell A1.
This means if you add another list of data in column F it will also be automatically included in the data validation lists.
Step 2 is much like step 1 in that you set up another named formula. This time it is called ‘Choice’.
This named formula is used to generate the validation list for both the ‘Categories’ in column A and the ‘Choice’ in column B.
This formula behaves differently depending on which cell you have selected and the value in the cell immediately to the left.
To understand how it works we’ll look at it from the point of view of cell B2, where it is identifying the category in cell A2….:
And then finding the corresponding list on the tbl_data sheet for ‘Vegetable’:
Here’s the formula:
See how the cell references have an exclamation mark before them – !A2. This exclamation mark acts like a relative reference and allows you to use the same validation anywhere in the workbook.
Note: this referencing style is only allowed in the name manager.
This referencing style also means you need to select cell B2 before you set up the named formula like this:
And now to understand what the formula is doing (in English):
Warning: You might like to get some coffee or Red Bull before reading this part!
Essentially, Roberto has used the OFFSET function to generate a range for the list of vegetables.
Here is the formula again so that it’s easy easier to cross reference the syntax to the English translation:
And in English:
IF cell A2 is blank, then list the Categories, otherwise, find the range of cells that contain the list for the matching category. Start by finding the first cell in the range, which will be the cell that matches the value in A2 in the Categories list (tbl_data!C3), then go down one row but stay in the same column, then count the number of cells that contain data, again by finding the cell in the Categories list that matches the value in A2, then go down 1 row and count the number of cells containing data for the next 100 cells, this will give you the last cell in the range (tbl_data!C5).
Below are the functions used in the above Named Formula and their syntax. Click on the links if you want the English translations for the functions below:
Note: I’ve crossed out the ‘cols’ and ‘width’ arguments in the OFFSET function because these are omitted in Roberto’s formula as they’re not required.
There are two forms of the INDEX function: the array form which has the syntax INDEX(array,row_num,column_num), and the reference form (syntax above). In this formula Roberto has used the reference form.
The INDEX function in reference form returns the reference of the cell at the intersection of a particular row and column.
Note: I’ve crossed out the row_num argument because when an array contains only one row the corresponding row_num argument is not required. Remember the ‘Categories’ are only in row 2 on the tbl_data sheet.
The area_num argument is also crossed out because it is not required since the formula only references one area, which is ‘Categories’.
Let’s step through the evaluation of the formula in cell B2:
Remember, in cell A2 we have selected ‘Vegetable’ like this:
Step 1 evaluate the 1st INDEX & MATCH formula; what is the value in A2 and what range is it looking in:
Step 2 finish evaluating the 1st INDEX & MATCH formula and start evaluating 2nd INDEX & MATCH formula; convert remaining Named Formulas to cell references:
Step 3 evaluate the 2nd MATCH formula; what is the array and what column_num is the list in:
Step 4 complete evaluating the INDEX formula:
Step 5 evaluate the OFFSET formula:
Step 6 voila. The range containing the list for Vegetables:
Setting up the Data Validation Lists
This is the easy part!
- Highlight the cells in both column A and B that you want validated.
- Data tab of the ribbon > Data Validation > Allow: List > in the ‘Source:’ field enter =Choice
Phew…there is a lot to take in with that tip but there are a few key points that I think are worth recapping:
- You can use the exclamation mark in the name manager to create a relative reference to a cell e.g. !A2. But only in the name manager.
- INDEX function; If a reference contains only one row or column, the corresponding row_num or column_num argument is optional.
- There is only one named formula that validates both the ‘Categories’ and the ‘Choice’ – clever eh!
- You can use this named formula in any worksheet in your workbook.
- Using this approach allows the greatest flexibility in your lists. Both categories and choices can be added or changed and they will dynamically feed through to your data validation lists.
Download the first Excel workbook. Note: this is a .xlsx file, please ensure your browser doesn’t change the file extension to .zip or you won’t be able to open the file.
One Last Thing…
As an alternative you can set your lists up in a Table and take advantage of the structured references with a formula like this:
Download the second workbook and take a look at how it works.
In response to a question in the comments below from Mike, Roberto has taken this trick to the extreme and created a way to allow new items to be added to the data validation lists on the fly.
Take a look at his magical solution here on his blog.
Incredibly it doesn’t even require VBA.
Thanks for sharing this tip, Roberto.
Vote for Roberto
If you’d like to vote for Roberto’s tip (in X-factor voting style) use the buttons below to Like this on Facebook, Tweet about it on Twitter, +1 it on Google, Share it on LinkedIn, or leave a comment to thank Roberto for taking the time to suggest this tip….or all of the above