Creating a drop down in data validation using indirect

[mrichard]

I am trying to do a drop down list using data validation. My scenario:

Lets say I have a drop down list of the State of MD and CA and I want to select one of these states. I will have another drop down list that has a named range that lists the cities for each state using the indirect formula.

I have this part working. So when I select a State either MD or CA in one cell (drop down list) I will only the see the cities from the state selected in another drop down.
My issue is when I change the State from lets say MD to CA the city drop down still shows the city from MD until I change the city in the drop down to the CA city list. My goal is that when I change from one state to another I want the city drop down cell to be blank until I actually select the new city.

I hope I have explained this properly.
Any help would be appreciated.

 

[mrichard]

Hey Fluff,
I copied your VBA stuff in a model in the worksheet but its not working.

What did I do wrong?

 

[mrichard]

Actually I tried again and now it works.
Thanks so much! You are awesome.

Mark

 

[Fluff]

You're welcome & thanks for the feedback.