dependent drop down lists in Excel

Q66. In Excel, how can I create dependent drop down lists?

For example, I have an employee database as shown below. I have set up a drop down list based on our Cities of our operation. However I would like to improve it so that the cities drop down list is based on the Country selected.

excel dependent drop down lists

To do it in Excel, here is the answer:

a) Set up named lists for each country listing its cities as shown below.

excel excel dependent drop down lists

b) Select all the rows in "City" column in Table. Click on "Data Validation" under "Data". Click on "Data Validation" again.

excel excel excel dependent drop down lists

c) In the "Data Validation"dialog box that pops up, against source enter the formula =INDIRECT(SUBSTITUTE(N3," ","")) where N3 corresponds to the first cell in Country column. Click OK.

Range "Names" cannot have spaces. Hence when naming ranges for each country space is removed. SUBSTITUTE function is used to remove the " " characters in the Country names while looking for the named range.

excel excel excel excel dependent drop down lists

d) Based on the value in "Country" column, the cities column brings up an appropriate list.

excel excel excel excel excel dependent drop down lists

 

You can find similar Excel Questions and Answer hereunder

1) How do I update my DropDown list whenever the sheet is activated?

2) How can I set up a drop down list?

3) Given a raw data Table, how can I find the value of a field for a specific value of another field?

4) Vba list all files in a folder in Excel

5) How can I display fractions as it is in a cell (instead of displaying as a decimal)?

6) How can I prevent users from entering duplicate values in a range?

7) How do I get the rank of a number in a list of numbers?

8) How to center excel sheet horizontally and verticaly on page in Excel

9) Applying a countif formula only to visible cells in a filtered list in Excel

10) How do I add a symbol like Triangle / Inverted Triangle for indicating trends in a cell using VBA?

11) Is there a way I can recover unsaved work?

12) How can I copy and rename a WorkSheet using VBA?

13) How can I find the number of working days between 2 dates given a holidays list?

14) How can I list all files in a folder using VBA?

15) How do I enter a formula in a cell using VBA (using Relative Reference)?