Dependent Drop down list with DEPENDENT DEFAULT VALUE

Hello, all
It must be pretty simple to do it but I'm lost..

Situation:
I have two drop down lists 1) Country and 2) States/province

Country has list: [US, Canada]

If "US" is selected, [Arizona ,Florida, NewYork] is listed in the 2nd drop down list
If "Canada" is selected, [Alberta, Ontario, Quebec] is listed in the 2nd drop down list

Scenario:
First, I select "US" and choose the states to "Florida"
Next I change the country to "Canada" and forgot to choose province
Then, the 2nd dropdown list is changed to [Alberta, Ontario, Quebec],
BUT the current value is still "Florida"

Probem:
Now I have "Canada" and "Florida" selected in the sheet

Question:
If the primary list is changed, can I make the 2nd drop down list to show a default value (e.g. blank or the first entry i.e. Alberta)?
Could someone help? I have a serious problem with this issue. Example EXCEL file is attached below

Thanks in advance

This can be accomplished with some short VBA code in the worksheet.
Right click on the worksheet tab and paste this code into the code window

Please Login or Register  to view this content.

Does that work for you?

Sorry, don't know of another way to do it without VBA.

You could create a formula in the cell first, before the Data Validation is applied.

=LOOKUP(A2, {0,"Canada","US"},{"","Alberta","Florida"})

Once that formula is in place, you could then apply the data validation to give you the drop down list in the same cell.

However, the first time you use the drop down to select an option OTHER than that offered automatically by the formula...the formula is forever erased. The DV active in that cell would also probably inhibit your attempts to re-enter the formula.

So this solution works fine for getting a "default" answer to appear in a row of data the first time. If you edit the choice to one of your own, you're making a permanent deletion of the formula on that row, any other rows further down your data still waiting for data would still have the formula in them, etc.

So, if this is meant to be used in the same two cells over and over again, it won't work. VBA would be the only avenue for recycling this behavior in the same cells over and over again even if you've made other manual choices in the interim.

Then something like this:

Please Login or Register  to view this content.

If that takes care of your need, be sure to EDIT your original post, click Go Advanced and add [SOLVED] to the start of your title. (unless the PREFIX box is still available, if so, set that to SOLVED.


(Also, use the blue "scales" icon in our posts to leave Reputation Feedback, it is appreciated. It is found across from the "time" in each of our posts.)

Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.