Select Subset of Table in List Box

I have 2 List Boxes, State and City. I am trying to select a city in a list box that is based upon a set value filtered by state. i.e. if I have list of 50 states and list of 1000 cities, I want to be able to select a state then select a city within that state (only see the cities within a selected state).

Any help is greatly appreciated.

Is a macro solution acceptable?

My first instinct is to do this with a macro. I would replace your List Validation/dropdown boxes (list boxes is not exactly the right term for these) with comboboxes and write code to respond to changes in them.

Someone who is really a wiz at formulas might be able to come up with a way to do this without macros but I'm wracking my brain on that.

Thanks 6StringJazzer

I looked at the code to shorten the city list and that is spot on. That allows me the select the correct city. How can I reference a cell to the info in the "Select City" combo box? I need to be able to filter a report by city as well as include the city in pivot tables.

Jim

Try this formula method using dynamic named ranges.
Note Data in G13:Hxx must be sorted by Column G (State)
The list must be continuous.
"xx" can be any number.

Name:= States
Refers To:=

Please Login or Register  to view this content.

Name:= CitiesByState
RefersTo:=

Please Login or Register  to view this content.

Data Validation B13
Allow:= List
Source:=

Please Login or Register  to view this content.

Data Validation B14
Allow:= List
Source:=

Please Login or Register  to view this content.

For some reason I can't edit the last post to add the attachment I thought I posted.
Here it is

Here is an improved solution for your problem.

This should be much quicker than my original proposal, it no longer uses the volatile function OFFSET() and includes a dynamic array to create a new list of states by removing duplicates from your data table.

Just add the State Code to Column G, and the city to Column H, then sort on Column G.
Any new state code added to "G" will automatically be added to Column E.

INDEX() is a really powerful non-volatile function, in most applications.
See this article for more information
The Imposing INDEX

Hope this helps

Okay, happy to have helped so far, got to go for a while, I'll look again later.
Have you seen my last post?
The timing of your last post and mine was amazingly coincidental!

[EDIT]
And again!!!!!
Haven't had time to consider it yet. Will be back later.

- the title of this thread doesn't match the content of the question: that is about dependent validation, not listboxes.

In this case I'd prefer a VBA-approach (see the attachment).
In this attachment I made a separate worksheet containing the 'database'.

This workbook is a small alteration to the one in Post#11, see there for more info.
Drag the Columns A:B down to extend the validation rows. (Note the difference in the $ "Anchors" in the names)

Not sure what you need in Column C, Sheets "Food" and "Sites" appear to be the same.
If you need more like that then just copy the Sheet "Food" then rename it to your needs, Column C header will change to suit.

Best if you can stay away from VBa if you can, many companies do not allow macros on their systems, and of course they will not work if the book is opened with macros disabled.

Hi Marcol -

I cannot get the curly brackets on Lookup!A2 to copy over (only the formula copies)

And....my group asked if they could add an additional field "Region" so that the sequence would go 1) Select Region, 2) Select State 3) Select City.

I added a new file for you.

Thanks for your help.

Happy to have been of some help.

Sorry I missed your earlier post, but you solved it yourself, that's a good way to learn!