Dynamically select validation list

[SJohnE]

Greetings,
I am trying to figure out how to select which validation list to use based on value in another cell.

Background: I have four lists on a second worksheet in the same workbook. The first list provides names of states (Oregon, Washington, California). The second list is a list of names for the state of Oregon, the third is for the state of Washington and the last list is a list of names for the state of California.

For example, in Column A I have a validation which provides a drop down for the State, it uses the first list mentioned above. In Column B I want to select which validation list to use based on the value in column A for the same row. So if in cell A1 the value is Oregon, I only want to see the names for oregon in the drop down for cell B1.

Can anyone explain how to do this? I have tried google and help but I am not having any luck. The lists all have range names associated with them.

 

[SkipVought]

Hi,

Your proposed method is flawed.

You can have TWO tables. First for the Stated And the second that has BOTH State and City columns.

For instance...
[tt]
State City
CA Los Angeles
CA San Francisco
CA Sacrimento
OR Portland
OR Salem
WA Tacoma
WA Olympia
WA Seattle
WA Vancouver
[/tt]
Then, use the OFFSET function as such, with the following assumptions:

1. Using Named Ranges
2. SelectedState is the Named Range for the selected state.
3. SelectedCity is the Named Range for the selected city.
4. The upper left cell for the table is A1.
5. This is the formula for the second validation--LIST.
[tt]
=OFFSET(A1,MATCH(SelectedState,State,0),1,COUNTIF(State,SelectedState),1)
[/tt]


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}