Microsoft Excel Training- From Beginner to Expert in 6 Hours/ EXCEL DASHBOARD REPORTS

Dependent Validation Lists

| | Information Helpful? Why Not Donate.

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

Got any Excel Questions? Free Excel Help

Create an In-Cell Validation List - Video

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.

Download 2 Free Demos . One is the basic, as explained here, the other is a more advanced version.

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. See Use Validation List on Another Worksheet

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.

Download 2 Free Demos . One is the basic, as explained here, the other is a more advanced version.

Excel Dashboard Reports & Excel Dashboard Charts 50% Off Become an ExcelUser Affiliate & Earn Money

Special! Free Choice of Complete Excel Training Course OR Excel Add-ins Collection on all purchases totaling over $64.00. ALL purchases totaling over $150.00 gets you BOTH! Purchases MUST be made via this site. Send payment proof to [email protected] 31 days after purchase date.

Instant Download and Money Back Guarantee on Most Software

Microsoft Excel Training- From Beginner to Expert in 6 Hours/ EXCEL DASHBOARD REPORTS

Excel Trader Package Technical Analysis in Excel With $139.00 of FREE software!

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