You can use your favorite social network to register or link an existing account:
Or use your email address to register without a social network:
Sign in with these social networks:
Or enter your username and password
Forgot your password?
Yes, please link my existing account with for quick, secure access.
No, I would like to create a new account with my profile information.
Today’s author is Reinout Dorreboom from the Netherlands, a Technical Consultant at Getronics, where he has worked with Office applications for many years, and where his Microsoft Certified Training skills enabled him to help other people get up to speed with Excel.
In Excel 2007 (and earlier), it’s possible to create a drop-down list. By using the INDIRECT function, you can then create additional drop-down lists that are conditional to the first drop-down list.
In this example we’ll create the following table:
Abraham van Stolkweg
Bacon Street (E1)
Columns B, C, and D contain drop-down lists. If you select Netherlands in the drop-down list in column B, only Dutch cities will be displayed in the drop-down list in Column C. If you select Germany, only German cities will be displayed in the drop-down list in Column C. Similarly, when you select a city in Column C, only the street names of that city will be displayed in column D.
To create conditional lists, complete the following procedures.
First, we’ll create the table as shown below.
Now, we’ll create a few lists.
First, we’ll create a list of countries. In this example, we create the following countries:
As you can see I made a horizontal list instead of a vertical list. This isn’t really necessary, but it is easier to maintain to keep the cities under the right country.
Second, we’ll add three cities to pick from for each country.
Cell F2: Amsterdam
Cell G2: London
Cell H2: Bonn
Cell F3: Rotterdam
Cell G3: Canterbury
Cell H3: Berlin
Cell F4: Eindhoven
Cell G4: Manchester
Cell H4: Munich
Now, we’ll create a list of streets for each city. In the example, you can then choose from two streets per city.
Cell F8: AmsterdamStreet1
Cell G8: LondonStreet1
Cell H8: BonnStreet1
Cell F9: AmsterdamStreet2
Cell G9: LondonStreet2
Cell H9: BonnStreet2
Cell F11: RotterdamStreet1
Cell G11: CanterburyStreet1
Cell H11: BerlinStreet1
Cell F12: RotterdamStreet2
Cell G12: CanterburyStreet2
Cell H12: BerlinStreet2
Cell F14: EindhovenStreet1
Cell G14: ManchesterStreet1
Cell H14: MunichStreet1
Cell F15: EindhovenStreet2
Cell G15: ManchesterStreet2
Cell H15: MunichStreet2
The worksheet should look like this now:
Ok, all content is provided. Now we can start creating a name for each range.
For information about how to define names, see Define and use names in formulas.
After defining the names, we can create the drop-down lists.
First, we’ll make a drop-down list for Country.
1. Select cell B2.
2. On the ribbon, click the Data tab.
3. In the Data Tools group, click Data Validation.
4. Fill the values as follows:
In the Allow box, select List. In the Source box, type =$F$1:$H$1 Note: Dollar signs ($) are used in the formula so that we can drag the cell downwards from David to Peter.
5. Click OK.
6. Drag the cell content downwards.
Now, we’ll make the first conditional drop-down list.
1. Select Cell C1.
In the Allow box, select List. In the Source box, type =INDIRECT($B2) Note: In the formula, a Dollar sign ($) is not placed before the row. That is because we want to drag the cell formula downwards.
5. Click OK. Note: If you click OK, you may get an error message indication that the evaluation of the formula was an error. This is correct, because the cell where this list depends on (B2) is empty.
6. Drag the cell content downwards.
Now, we’ll make our second conditional drop-down list.
1. Select cell D2.
In the Allow box, select List. In the Source box, type =INDIRECT($C2) Note: In the formula, a Dollar sign ($) is not placed before the row. That is because we want to drag the cell formula downwards.
5. Click OK. Note: If you click OK, you may get an error message indicating that the evaluation of the formula was an error. This is correct, because the cell on which this list depends (C2) is empty.
Done! If you choose a country, the City field displays only cities for that country. And only streets for the selected city will be shown.
You have a misstype in the data validation for City. one have to select cell C2, not "Select Cell C1" as you said.
Any link to download the file?
Be Careful that Country or City names don't have spaces in them or else it wont work
Using an _ instead of a space fixes it
I would enhance it so that if country changes, then the corresponding city field should be set to "* TBD" [otherwise the old city is out of sync with the new country]. Do the same thing if the city changes. I would then conditionally format the cells to highlight the "* TBD" cells.
In the code below, the names "Country", "City" and "Street" refer to cells B2:B4, C2:C4 and D2:D4, respectively.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim r As Long
Dim c As Long
Dim Cell As Range
Dim rngCountry As Range
Dim rngCity As Range
Dim rngStreet As Range
Set rngCountry = Range("Country")
Set rngCity = Range("City")
Set rngStreet = Range("Street")
For Each Cell In Target.Cells
r = 0
c = 0
If Not Intersect(Cell, rngCountry) Is Nothing Then
r = Cell.Row
c = rngCity.Column
ElseIf Not Intersect(Cell, rngCity) Is Nothing Then
c = rngStreet.Column
If r <> 0 And c <> 0 Then
' clear dependent cell
Cells(r, c).Value = "* TBD"
its a nice tip,
i created with other test name...i have a pax filtration of adult,child&infant. i need to display date of birth in col C2, if i select infant in B2.it worked, after selecting infant and 'date of birth' in c2, i need to change b2 as 'adult' for which i do not have any optional selection in c2. the earlier selection of date of birth is not disappearing...
(selections are only 'date of birth' for c2)
could u revert pls
"I would enhance it so that if country changes, then the corresponding city field should be set to "* TBD" [otherwise the old city is out of sync with the new country]. Do the same thing if the city changes. I would then conditionally format the cells to highlight the "* TBD" cells."
Better would be to use the first value in each list, so that the user doesn't have to select something 100% of the time. The user would obviously be aware that he/she can make other choices from the drop downs. This is not uncommon behavior in data entry forms.
A few more countries & cities can result in city name explosion. One solution would be to create a city/street table, sorted in ascending order by city. You can then create a lookup formula that selects only the street range for the appropriate city, thus eliminating all of the city names.
I'm not a fan of always defaulting a value to the first value in the list. My philosophy for whether to default a value or not depends on a combination of:
1. What are the chances that I can guess the value correctly? If I can't guess it with a sufficiently high degree of confidence, then I don't default it to anything. For example, if one option is used 10% of the time and all the others are only used 5%, the "big" option isn't "big" enough for me to default to it.
2. What's the risk if the user doesn't notice that the value has been defaulted and blindly accepts my default? If this process was to delete information for a city, I wouldn't want to risk that the user blindly accepts my default city.
Its nice being a member of this group. Because I found it so interesting learning on line. especially on Microsoft excel.
Can you provide a link to the example so that we can see it in action.
this can be actually done totally dynamically. I mean that it's not necessary to create named ranges for every set of values so that you can freely add new values and they are automatically considered in the validation. It can be also combined with an array function retrieving distinct values from a 'raw' list so that you can feed your validation list from basically any source of data.
The validation formula is then of course a bit more complicated but it works perfectly.
I'd send a sample file but I don't know where.
Below is how the validation works but without the excel it's not very understandable.
"What does it do:
- takes value in cell I26 (the practice for which we are creating list of CCs) and finds it's position in the CCsHeader (the first MATCH function)
- creates a range of the CCs under the selected practice (Microsoft in our case) (the outter OFFSET function)
- the inner COUNTA function (and everything inside) counts number of CCs under the selected practice. It's pretty the same as the stuff before that but we need to limit the area where to count the values (max. 14 values in our case but it can also be e.g. whole column)."
Exactly the same thing as the CC validation except that it looks for range of Names under the CC selected in cell J26"
I can not proceed following:
4. Fill the values as follows:
In the Allow box, select List.
In the Source box, type =INDIRECT($C2)
Unknown valus but not question if I want continue.
Any idea why?
Can u show the table and one formula somehow?
Add the following EVENT to your workbook, when you change your second option (2nd data validation option), the 1st option will dissapear.
eg. when you selected the wrong city 1st time, you
re-select again, the country will appear blank immediately. That means you need to re-select again the correct country based on the re-selected city.
Application.EnableEvents = True
If Not Intersect(Target, Range("Q2")) Is Nothing Then
Application.EnableEvents = False
Range("Q3").Value = ""
Application.EnableEvents = True
Any doubts refer to the mrexcel for more information & solution.
I can get this to work great without using an underscore or period, but entering anything other than a single value and the drop-down box fails to open??
indirect work well with drop down. however,it is not working with combo box. any one can help me how to use indirect in combo box