Home > Validation > Dependent > INDEX Create Dependent Lists With INDEXAs 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. |
Add More Headings and Entries to ListsEnter 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) Create the ValData Dynamic RangeOn 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. |
Create a CounterThe 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.
Create the UseList Dynamic RangeThis 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.
|
Why the Cursor Placement is Important
That is why it is so important to place the cursor in the correct starting cell when creating this named range. |
Get the Sample FileGet a sample Excel data validation file About the DeveloperRoger 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 |
Related TutorialsDependent Drop Down Setup Choices |
Last updated: December 29, 2022 2:58 PM