EXCEL VIDEO TUTORIALS / EXCEL DASHBOARD REPORTS

Ozgrid, Experts in Microsoft Excel Spreadsheets

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

See Also: Stop/Prevent Duplicates in Excel || Stop/Prevent Blanks in Excel || Decreasing Validation Lists ||  Multi-Table Lookup

Create an In-Cell Validation List - Video

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.

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 special@ozgrid.com 31 days after purchase date.


Instant Download and Money Back Guarantee on Most Software

EXCEL VIDEO TUTORIALS / 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

Some of our more popular products are below...
Convert Excel Spreadsheets To Webpages | Trading In Excel | Construction Estimators | Finance Templates & Add-ins Bundle | Code-VBA | Smart-VBA | Print-VBA | Excel Data Manipulation & Analysis | Convert MS Office Applications To...... | Analyzer Excel | Downloader Excel | MSSQL Migration Toolkit | Monte Carlo Add-in | Excel Costing Templates