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:
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
