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.

 

[kweaver]

This is a dependent drop down issue.
First, create your state drop down in, say, C2.

Then, after you have your cities for MD and CA in, say, columns H and I, respectively (or elsewhere), select those.
From the formula tab, select Define Name, then Create From Selection, and reference only the TOP.

Then, in the cell, say D2, create drop down for the cities list and the reference would be: =INDIRECT(C2)

Does that help?

Sorry about not focusing on the "goal"...which can't be done without VBA as Fluff points out.

 

[Fluff]

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.

You would need VBA for that. Is that okay?

 

[mrichard]

You would need VBA for that. Is that okay?

VBA is fine. Can you suggest something in VBA?

I do not use VBA often.
Thanks

 

[mrichard]

This is a dependent drop down issue.
First, create your state drop down in, say, C2.

Then, after you have your cities for MD and CA in, say, columns H and I, respectively (or elsewhere), select those.
From the formula tab, select Define Name, then Create From Selection, and reference only the TOP.

Then, in the cell, say D2, create drop down for the cities list and the reference would be: =INDIRECT(C2)

Does that help?

Sorry about not focusing on the "goal"...which can't be done without VBA as Fluff points out.

Thanks.

 

[Fluff]

Where are the state & city drop downs?

 

[mrichard]

Where are the state & city drop downs?

Maybe this will help:

 

[Fluff]

Ok, how about

Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.CountLarge > 1 Then Exit Sub
   If Target.Address(0, 0) = "I5" Then
      Range("K5").Value = ""
   End If
End Sub

This needs to go in the sheet module for that sheet.

 

[mrichard]

Ok, how about

Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.CountLarge > 1 Then Exit Sub
   If Target.Address(0, 0) = "I5" Then
      Range("K5").Value = ""
   End If
End Sub

This needs to go in the sheet module for that sheet.

I will give it a try. Stay tuned

Thanks

 

[Fluff]

Ok