You can use data validation to create drop down lists in Excel. With a bit of Excel magic, you can create dependent drop down lists, so the selection in one drop down controls what appears in the next drop down.

We'll take a quick look at how a basic dependent drop down works, then add flexibility by changing the data validation formula.

Select Countries and Cities

For example, in the worksheet shown below, there's a drop down to select a country in column A. If you select USA as the country, cities from the USA appear in the Column B drop down.

This is created by using the INDIRECT function in the data validation settings for column B:

=INDIRECT(A2)

There are city lists in the workbook, and each list is named to match its country name. So, if you select USA in cell A2,

Continue reading Different Drop Down Lists in Same Excel Cell