Dependent Data Validation

[Andikins]

Hi All

Apologies if this has already been answered before.

My issue is as follows:

My first column is say "Country" and my second is "State".
The user will input multiple rows where the country can vary by row

For a row where Country = 'United States' I want to use data validation on the State field to restrict the user input to a list of US states

For a row were Country <> 'United States' I want the State field to be free input with no restrictions.

Is this possible using Excel Data Validation? I realise I could probably do something via the worksheet change event macro but would much prefer not to.

Thanks in advance...

 

[VoG]

This might help you http://www.contextures.com/xlDataVal02.html

 

[Andikins]

This might help you http://www.contextures.com/xlDataVal02.html

Thanks for the quick response VoG. That was the way I was planning to go. My problem is that I am not sure how to set the validation to be free format if the country is not US

 

[Andikins]

So just to clarify my plan is to do something like:

I have a list of all US states as a named range called "US_States".

Then if country was in column A I would do a validation with a formula something along the lines of:

=IF(a2="United States",US_States,XXXX)

the issue being that I am not sure what to put for XXXX to allow the user to input whatever they want when the country in not US.

 

[Andikins]

Sorry for bumping but was wondering if anyone had a solution?

 

[Biz]

Why not defined named range called USA?
For Instance, H2:H51


Use Indirect to call range then
Data validation

List

Source
Indirect(A2)
The indirect formula assumes A1 country.

Does this help?

Biz