As an alternative to using INDIRECT to create dependent Excel data validation lists, you can use the non-volatile INDEX function.
NOTE: There are other techniques for setting up dependent drop down lists, so choose the setup method that is best for your needs.
Thanks to Roger Govier, who created this tutorial and sample file.
In this example, just 4 dynamic range names are used. Of these, 3 are used to create the basic framework of the method.
The fourth permits any number of subsidiary lists to be created to act as Dependent dropdown lists from the entry in the previous column, without having to define individual names for each list.
Lists Sheet
Select sheet Lists and then go through the following processes to create the 4 Dynamic range names that are required:
Our first List is going to be called Master
Enter all the Region names as headings in cells B1:E1 of the Lists Sheet.
Then, under each Region heading, enter one or more countries from that region. (See screen shot below, or see the sample file)
On the Lists sheet is the range of data that will be used in all validations for the Data Entry sheet. Instead of referring to the sheet name as range for this data, which will grow as more validations are added, you'll create a Dynamic range.
The choice of 100 rows is arbitrary, and should just be sufficient to contain whatever is going to be the longest of your lists.
Next, because each individual List can have a different number of values, we need to create a Counter.
The Counter is a variable which defines the length of each individual List. It is dynamic, and will alter dependent upon which column is being used, the column being returned by the Match function. A dynamic range will be created for the Counter.
Note: The range is relative, and refers to the cell to the left (A2) to determine its values, so it is vital that you have the cursor in cell B2 of the Data Entry sheet when defining the range.
This is the universal list name that will apply to all lists that are added to the sheet other than the Master list as previously described.
As new columns are added to the Lists sheet, their relevant used range is automatically dynamically created, and using UseList as the data source will apply to the whole of the validation areas on sheet Data Entry.
Note: Because the range is relative, and refers to the cell to the left (A2) to determine its values, it is vital that you have the cursor in cell B2 of the Data Entry sheet when defining the range.
That is why it is so important to place the cursor in the correct starting cell when creating this named range.
Now that you've defined the dynamic ranges, the next step is to set up the validation ranges on the Data Entry sheet.
The next step is to set up the remaining validation ranges on sheet Data Entry
You can add as many extra columns as you wish on sheet Lists. Each column has to have a heading which is a member of one of the other columns on the sheet, so that the new column added will be Dependent upon that heading.
For example, you could add a heading for US, and type a list states in that column. Next, you could add the state names as headings in row 1, and type a list of city names for each state.
You do not need to define any new names, as the Uselist dynamic range name will automatically deal with that for you.
Get a sample Excel data validation file
Roger Govier is an Excel MVP based in the UK who undertakes assignments in Excel and VBA for clients worldwide. While he enjoys the intellectual challenge of solving problems with worksheet functions, Roger claims to be intrinsically lazy, so he always looks for a fast and simple way to provide solid workable solutions.
Find more of Roger's tutorials and sample files here: Sample Files - Roger Govier
You can contact Roger at:
roger@technology4u.co.uk
Technology 4 U, Glanusk Farm,
Llanfair Kilgeddin, Abergavenny, NP7 9BE, UK
Tel +44 (0) 1873 880266
Mobile +44 (0) 7970 786191
Dependent Drop Down Setup Choices
Dependent Drop Down from Dynamic Arrays
Dependent Dropdowns from a Sorted List
Last updated: July 9, 2021 3:40 PM