Data validation and default values

[Hall]

I'm using data validation with dependent dropdowns (thank you Debra
Dalgleidh) where if I choose from a dropdown in one cell, the choices
available in the adjacent cell's dropdown automatically limit based on the
first cell's value.

OK. Now I need one step further.

Can I have that second cell's value get a default value based on what I
choose in the first cell?

For example, my first cell is country, the second is state.

I can choose in the first cell from a dropdown a list of countries, and if I
choose "USA" in this list, then the second cell limits its dropdown to the
states of the "USA". That part works.

But I also want it to be that when I make that first cell "USA", then I want
the second cell's value to automatically become the first value among the
list of states "Alabama", and of course, I can use the dropdown in the
second cell to change that.

Can anyone tell me how this can be done?

Thanks!

 

[Debra Dalgleish]

Not quite automatic, but you could enter the default value as the first
item in each list of cities, so it would be the first selection in every
dropdown list.
Or, you could use a formula in the dependent cell, e.g.:
=IF(B3="","",OFFSET(INDIRECT(B3),0,0,1,1))
but that would be overwritten if the user selected a different value
from the dropdown list.