intelligent multi-dependent drop-down in excel?

1
votes

(Microsoft Excel Related) Hi,

I have a requirement where I have country, state and city. I have to make a dropdown on column A for country, respective state on column B and respetive city on column C.

For example: I have 2 countries to show on column A => India, United Kingdom Then, as per country selected, I have to show states. Eg: Mah, Guj and Kar for India, while Yorkshire, West Midlands and Bath. Further down, I need to have some cities for respective states as Mah: Mumbai, Pune Guj: Ahmedabad, Surat Kar: Banglore Yokshire: Sheffield, Doncaster West Midlands: Birmingham, Solihull, Coventry Bath: Bristol

I am able to achieve this by doing a search on google for "multi-dependent drop-down in excel" and finding way to define name and then daatvalidation, INDIRECT function, etc.

The problems I face is: 1) When I select a country(say India), then select State(say Mah), then again change country (say to United Kingdom), the state must get blank, since country is changed. similarly the city must get blank when state is changed.

2) While defining name, I am not able to define names with '/' and ' '(space), ut I need to display such text in drop-down. This currently is not achievable.

There are key problems without which I am not able to proceed. Please help. Many Thanks.

2
excelexcel-2007

2 Answers

2
votes

1) Conditional Formatting

You can use conditional formatting in the subsequent cell to test the current cell's value and make sure it is a MATCH to the range of options that are the current drop list. If the value in the cell no longer matches the drop list, you can format the font to match the background, making it look like the cell is empty, even though it isn't, really.

If you then use the drop down to select a new matching value, the font color returns to normal.

2) VBA

You can employ a worksheet_change macro to watch those columns and empty the subsequent cells when you make a new selection in a prior cell.

Here's a link to some sample files: Dependent Drop Downs

Take a look at he file DependentList3.xls, it demonstrates both of those techniques further.

0
votes

1) You must add a State to your second drop-down (example: "Select state...") and select this value when you want to reset State's value. (Same strategy for Cities);

2) Can you provide the code you used to populate your dropdownlist?