+ Reply to Thread
Results 1 to 7 of 7

Cascading Data Validation Help Needed

  1. 03-09-2017, 07:48 PM #1
    snapfade
    snapfade is offline
    Forum Contributor
    Join Date
    10-08-2012
    Location
    San Clemente, California
    MS-Off Ver
    Office365
    Posts
    383

    Cascading Data Validation Help Needed

    Hi,

    Column A of my sample worksheet has a dropdown for State, which is based on the list of states in Column E.
    Column B of my sample worksheet NEEDS a dropdown for City, which is based on the list of States and Cities in Columns H and I.

    I have added a graphic to show what I would like to see for Column B. The state chosen is Alabama so the dropdown for City needs to show the list of cities in Alabama.

    Can anybody help with this?

    I have seen similar solutions but not one in which the second dropdown needs to selectively filter the City list to match the state.

    Thanks for looking!
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 03-09-2017, 08:35 PM #2
    humdingaling
    humdingaling is offline
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,636

    Re: Cascading Data Validation Help Needed

    a bit of setup involved to do it but all can be found here

    http://www.contextures.com/xlDataVal02.html
    If you are satisfied with the solution(s) provided, please mark your thread as Solved.
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.
    Reply With QuoteRegister To Reply
  3. 03-09-2017, 09:00 PM #3
    snapfade
    snapfade is offline
    Forum Contributor
    Join Date
    10-08-2012
    Location
    San Clemente, California
    MS-Off Ver
    Office365
    Posts
    383

    Re: Cascading Data Validation Help Needed

    Thanks humdingaling, but that solution does not work. The second dropdown needs to select all alabama cities from a long list of cities. The example cited would require me to have column for each state with its cities beneath. I need a solution that will take the first dropdown (Alabama) and find all cities next to Alabama in the other table.

    Any other thoughts?

    Thanks for looking in!
    Reply With QuoteRegister To Reply
  4. 03-09-2017, 09:14 PM #4
    MarvinP
    MarvinP is offline
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,168

    Re: Cascading Data Validation Help Needed

    Hi Snapfade,

    I have another way to do this problem using a little VBA magic and dynamic named ranges along with Advanced Filters. See the attached. Note all dropdowns in Column B refer to a Validation List called "=City". This "=City" is a dynamic named range of what is in column M. All you need to do is rebuild column M with the state selected. To do that I've created a "double click" macro to rebuild the advanced filter list with what is in column A, next to the cell you double click in. Look at the attached. Ask questions and look at the VBA behind the sheet and in the macro. Look at the Formula Tab > Names.

    Cascading Dropdowns using Advanced Filter.xlsm
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.
    Reply With QuoteRegister To Reply
  5. 03-09-2017, 09:51 PM #5
    humdingaling
    humdingaling is offline
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,636

    Re: Cascading Data Validation Help Needed

    in named range
    set "DV" to
    Formula: copy to clipboard 
    Please Login or Register  to view this content.


    set Data Validation to "DV"

    ps this only works when all the states are all clumped together
    Attached Files Attached Files
    Last edited by humdingaling; 03-09-2017 at 09:54 PM.
    Reply With QuoteRegister To Reply
  6. 03-10-2017, 11:13 AM #6
    snapfade
    snapfade is offline
    Forum Contributor
    Join Date
    10-08-2012
    Location
    San Clemente, California
    MS-Off Ver
    Office365
    Posts
    383

    Re: Cascading Data Validation Help Needed

    An amazing use of Data Validation. Highly recommend!
    Reply With QuoteRegister To Reply
  7. 03-12-2017, 07:27 PM #7
    humdingaling
    humdingaling is offline
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,636

    Re: Cascading Data Validation Help Needed

    Thanks for the rep
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Cascading validation
    By kieranm105 in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 10-05-2016, 12:28 PM
  2. ActiveX Combobox VBA not working on Cascading Data Validation
    By ringonohitorigoto in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 10-03-2016, 03:33 PM
  3. [SOLVED] Creating cascading data validation lists/combo boxes without named ranges
    By mtpyankee in forum Excel General
    Replies: 3
    Last Post: 07-28-2014, 03:16 PM
  4. Workaround for Cascading Data Validation with Dynamic named ranges!!!!!
    By magicbob007 in forum Excel General
    Replies: 5
    Last Post: 07-28-2014, 11:53 AM
  5. [SOLVED] Cascading Validation Lists
    By lyla22 in forum Excel General
    Replies: 10
    Last Post: 07-21-2014, 02:30 PM
  6. Excel 2007 : Cascading Validation lists
    By ellsworth2000 in forum Excel General
    Replies: 7
    Last Post: 05-03-2011, 07:00 PM
  7. Cascading Data Validation -eliminate duplicates
    By coachcr in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 10-15-2008, 11:39 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1