Excel Made Easy

Dependent drop down lists in Excel

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.

To do it in Excel, here is the answer:

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

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

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.

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

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 list all files in a folder using VBA?

3) Vba list all files in a folder in Excel

4) Here an explanation about list box and how to control list boxes in Excel VBA

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

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

7) Generate a list of unique values in Excel

8) How can I set up ListBox using VBA to allow users to select multiple values?

9) How can I identify the cells that are dependent on a particular cell?

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