OzGrid

Dependent Validation Lists

< Back to Search results

 Category: [Excel]  Demo Available 

Dependent Validation Lists

 

Dependent Validation. Excel Data Validation List Based on the Value of Another

Got any Excel/VBA Questions? Free Excel Help

 

Dependent Data Validation Lists

A common question from Excel users is along the lines of; I have a list in a cell that has used Data>Validation with the "List" option. I would like to have another cell display a list that is depended on the item chosen in my first list.

There are quite a few ways to achieve this, but the easiest by far is like this.
Open a new Workbook and on Sheet1 enter these heading into cells A1:D1

Expensive Cars, Cities, Names, Countries

Now place some relevant entries below these headings, down to say row 5. Now select A1:D1, click in the Name Box (left of the formula bar) type the name: List1 and push Enter. Now select A1:D5 (or the last row of the longest list) and go to Insert>Name>Create. Ensure only "Top Row" is checked and click Ok. If you now go back to the Name Box you will see 5 Named ranges. Note how Excel has used the Underscore for "Expensive_Cars". This is because named ranges cannot have spaces.

Now click onto Sheet2 and select cell A1. Go to Data>Validation, choose the "List" option then type: =List1 in the Source box. Ensure "In-cell dropdown" is checked and click Ok. Now select cell A2 and again go to Data>Validation, choose the "List" option then type: =INDIRECT(SUBSTITUTE($A$1," ","_"))

in the Source box. Ensure "In-cell dropdown" is checked and click Ok.

Now select a list from cell A1 and you will get the appropriate list in cell A2.

There are a couple of points that we should understand. Normally, a Validation list cannot refer to a list from another Worksheet. This can be overcome by naming the list and using its Name over its address.

The INDIRECT Function return the reference returned by a text string. This means, rather than the Validation List in A2 seeing the content of A1 as a text string, it sees it as a range name .

The SUBSTITUTE Function is used to replace any spaces in the text of A1 with a Underscore. This is vital when we have items in List1 like: Expensive Cars.

 

 

See also: Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions and Index to new resources and reference sheets

 

See also:

Debug VBA Code
Debug, Evaluate/Audit & Fix Excel Formula Errors
Decreasing Validation Lists
Excel Formulas/Formulae, Tips, Tricks and Feature Examples
Excel Charts : Delete a Single Entry From Excel Chart Legend

 

Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.

 

Microsoft ® and Microsoft Excel ® are registered trademarks of Microsoft Corporation. OzGrid is in no way associated with Microsoft.


Gallery



stars (0 Reviews)