Our goal here, is to create a dropdown list by using Data Validation, where the contents of the list will be determined by another Data Validation Dropdown list. Here’s an example…
Now there are two dropdowns here, one for the Band and another for the Song Title. But the Song Title dropdown should reflect our choice in the Band dropdown. So if I choose The Beatles as a Band, I want my Song Title dropdown to begin with Please Please Me and go through to Here Comes The Sun.
But if I was to choose Vintage Trouble as a Band, a different story should be seen…
Ok, now that we know where the train is heading, let’s get moving. If you want, you can follow with the original file
Dependent Data Validation dropdown lists.xlsx
We will achieve this in three steps.
First we will create a dropdown list in cell A3 to enable the band selection. Before attempting this, make sure, that the band names do not include spaces! It’s very important that the spaces were replaced with underscores. So instead of The Beatles, we have The_Beatles. It’s not that this is the only way, but there can be no spaces! I will explain later on, why that is. Now back to the first dropdown.
Select cell A3 and Data/Data Validation
And you get the following textbox
Where you mark the cells with the Band Names as the Source. Press Ok and you should get…
And now for the hard part or step 2. In our data table, all columns of data should get their names from the top row (the band names). We will let Excel do the hard work here. First, we select the entire table with the band names and song titles. Then we go to FORMULAS/Create from Selection and in the dialog box select Top Row, since the band names in the top row are the names we wish to give to the ranges.
After you press OK, you should check the Name Box dropdown if all three names were applied. Do not be surprised by the sort order because it is alphabetical and can differentiate from the sort order in your table. The main thing is that the names were given and are the same as the ones in the top row of our table. With the underscores and all! If you didn’t follow the instructions for the band names, they must not contain spaces. If they did, spaces were automatically replaced with underscores by the Create from Selection command. If they are different now, correct the names in your table to match with the names of the ranges.
And now for our final step. Creating the second dropdown. Select the cell where you want the second dropdown to be (B3 in our example) and go to Data/Data Validation and then put =INDIRECT($A$3) as a Source
And that’s the whole magic. Since the A3 Cell contains the name of the range, the INDIRECT function understands that that range should be the source for the second dropdown. And when you change the selection in A3, the dropdown in B3 changes accordingly.
If you manage to combine this with the knowledge of Dynamic Named Ranges in Excel, then you really have something very powerful!
The one thing that can be unpleasant at this point is that if you already selected a song from the second dropdown, and you then change the band selection, although the second dropdown will change, the cell content will not. So the song you originally selected will remain there. It can be changed of course, but it is a nuisance brought about by the fact that Data Validation will check everything you put into that cell from this point on, but it will not check if what is already in a cell is compliant with the new validations. But this can be overcome by the use of VBA. If you are not comfortable with the use of VBA, don’t worry, this is not required for the dependent dropdown list to work, it’s just a cosmetic thing.
To make it work you will need two pieces of code. The first is very short and sweet. It’s a simple declaration of two variables that Excel needs to remember even when the code is not running.
Global Bnd As String Global acheck As Integer
Now for the main code you need. This one you place into the WorkSheet code and attach it to the WorkSheet Change event.
Here’s the code (it relies on the example that is in all the screenshots. The placement of the dropdowns is important. If you have placed the two dropdowns differently, then you must change those ranges (A3 and B3) in the code). Do not copy this code (you can read why in the second comment bellow the post. Copy it out of this working sample of data validation with VBA.
Private Sub Worksheet_Change(ByVal Target As Range) Application.ScreenUpdating = False Dim Celica As Range If acheck = 1 Then acheck = 0 Exit Sub End If acheck = 0 If Bnd = "" Then Bnd = "The_Beatles" End If If Range("A3").Value <> Bnd Then For Each Celica In Range(Range("A3").Value) s = 0 If Celica.Value = Range("B3").Value Then s = 1 Exit For End If Next Celica If s = 0 Then acheck = 1 Range("B3").Value = "" Exit Sub End If Bnd = Range("A3").Value End If Application.ScreenUpdating = True End Sub
Probably the longest title of all times, but it leads to ...
Can you create a Data Validation Dropdown list that uses data ...
Creating a dependent or linked Dropdown list In this article I ...
This site uses Akismet to reduce spam. Learn how your comment data is processed.
VBA Code did not work in in my Excel 2013 as shown.
Here is the code to make the second box clear (where cell A3 is the Band and cell B3 is the Song title):
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Bnd As String
Dim acheck As Integer
Application.ScreenUpdating = False
Dim Celica As Range
If acheck = 1 Then
acheck = 0
Exit Sub
End If
acheck = 0
If Bnd = “” Then
Bnd = “The_Beatles”
End If
If Range(“A3”).Value Bnd Then
For Each Celica In Range(Range(“A3”).Value)
s = 0
If Celica.Value = Range(“B3”).Value Then
s = 1
Exit For
End If
Next Celica
If s = 0 Then
acheck = 1
Range(“B3”).Value = “”
Exit Sub
End If
Bnd = Range(“A3”).Value
End If
Application.ScreenUpdating = True
End Sub
Hi,
the code in the article is actually fine, but it doesn’t work because the rendering of the article replaces my ” with something that it shouldn’t. I have placed it in the html code tag and I thought it would help, but it does not. I’m still struggling to make it work… It did the same with your code… If you were to copy paste your code, it would take you to the debug mode. I will edit the article as to let people know, but I really must find a solution since I expect to have VBA code in many of my articles in the future.
To insert code in plain text for your blog get instructions here: https://answers.yahoo.com/question/index?qid=20070111153406AAcj2C3
Hi, is there any way to create normal content in the excel worksheet, that is dependent on what we select on the dropdown list? i.e. the rest of the content below the dropdown list will change depending on the dropdown selection.
Hi Joanne,
I’m not sure I understood the question, but this is my take on the answer
The file
This will give you a link to the Table called Lookup on the Data sheet and on changing the Salesperson on sheet1, you get all the data bellow with the use of Vlookup. Check it out and let me know if it was helpful.
Yes it is! Thank you so much. Love this website btw 🙂
I’m glad to hear you like it Joanne.