Dependent drop-down blox

[zdodson]

In Sheet 1, I have a "raw data" set. In Column A on Sheet 1, I have a list of schools. In Column B on Sheet 1, I have a list of competitors.

In Sheet 2, I have a drop-down menu that is able to retrieve the list of schools in cell A1. In a separate drop-down menu in B1, I want the drop-down menu to only retrieve the competitor names from a specific school. Here is an example:

School Name	Competitor Name
ACU	Jimmy Hills
ACU	Patricia Johnson
MU	Kyle Irving

<tbody>
</tbody>

If I were to select ACU from the first drop-down menu, I want "Jimmy Hills" and "Patricia Johnson" to be the only values that show up in the second combobox.

Is there a way to do this without having to create specific named ranges for every school? If so, which method would be better: formula, or VBA-based?

 

[tusharm]

Neither formula nor VBA. Use SQL!

For a tutorial see Cascading queries

In Sheet 1, I have a "raw data" set. In Column A on Sheet 1, I have a list of schools. In Column B on Sheet 1, I have a list of competitors.

In Sheet 2, I have a drop-down menu that is able to retrieve the list of schools in cell A1. In a separate drop-down menu in B1, I want the drop-down menu to only retrieve the competitor names from a specific school. Here is an example:

School Name	Competitor Name
ACU	Jimmy Hills
ACU	Patricia Johnson
MU	Kyle Irving

<tbody>
</tbody>

If I were to select ACU from the first drop-down menu, I want "Jimmy Hills" and "Patricia Johnson" to be the only values that show up in the second combobox.

Is there a way to do this without having to create specific named ranges for every school? If so, which method would be better: formula, or VBA-based?

 

[pareshj]

It is very simple to create dependent drop down list in excel. Check out the below link and follow the steps,

Create Dependent Drop-down lists in excel | S&P Infotech Blog

 

[tusharm]

The technique you describe has been documented since many, many years in the old newsgroups and any number of websites -- just search google/bing.

Unfortunately, the solution is neither elegant nor scalable nor extensible. There are about 170 countries. How much work would it require to create and maintain 170+ ranges for those countries?

Further, imagine a scenario where you have countries, then states within those countries, then counties (or their equivalent) in the states, and finally cities/towns/villages in those counties. How would you handle this setup?

A solution that is usable in a real-life application has to work with a table and not data scattered over dozens or hundreds or thousands of ranges, each of which requires manual tending.

My reference in this discussion uses SQL with a table. Another that I have used for 2 levels of dependent dropdowns uses a single range sorted in a specific manner, a Pivot Table, and two named formulas. I have to document this one. Hopefully, soon.

It is very simple to create dependent drop down list in excel. Check out the below link and follow the steps,

Create Dependent Drop-down lists in excel | S&P Infotech Blog