Change Validation Lists without VBA 2

[FractalWalk]

I need to create a spreadsheet with two drop down lists: State and city. I need the user to be able to choose a state in the first box and then based upon that result, the second box gives choices of cities only in those states. The trick is that I can't use any macros or VB.

Any ideas?

 

[SkipVought]

Hi,

You need 2 lists:

State list

State & City list - State & City in separate columns

When a State is selected from the first list, return the List Index
[tt]
=Match(SelectedState,StateList,0)
[/tt]
Then you COUNT how many cities are in that state in the SECOND list
[tt]
=Countif(State,SelectedState)
[/tt]
Then in Insert/Name/Define -- define a range named CityList with the following expression
[tt]
=OFFSET(Sheet1!$L$1,MATCH(SelectedState,StateList,0),0,COUNTIF(State,SelectedState),1)
[/tt]
where Sheet1!$L$1 is the cell containing the City Heading in the second list

Then use =CityList in your second validation dd.



Skip,

_{[red]Be advised:[/red] When you ignite a firecracker in a bowl of vanilla, chocolate & strawberry ice cream, you get...
Neopolitan Blownapart!}

 

[FractalWalk]

That isn't working for me. But maybe I am not implementing it correctly.

The first Match function gives me the state number N I am referrring to (i.e. 1 - 50). That is then used in the offset for the second list as if I want to start at the Nth row.

But that isn't the case. As state #1 has multiple cities in the second list, the Nth row of that list isn't the beginning of the selected state rather it is just the Nth city in the first state.

 

[SkipVought]

sorry,

You have to lookup the selected state in the SECOND list
[tt]
=OFFSET(Sheet1!$L$1,MATCH(SelectedState,State,0),0,COUNTIF(State,SelectedState),1)
[/tt]


Skip,

_{[red]Be advised:[/red] When you ignite a firecracker in a bowl of vanilla, chocolate & strawberry ice cream, you get...
Neopolitan Blownapart!}

 

[FractalWalk]

Yeah, I tried that and I kept geting #N/A(?)

What I did was add a 3rd column to my state city list and numberes the records. Then I used a Vlookup to find the starting index row.

That works for me. Thanks for all your help!