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.