+ Reply to Thread
Results 1 to 12 of 12

Drop Down Data Validation

  1. #1
    Registered User
    Join Date
    12-09-2016
    Location
    Irvine, CA
    MS-Off Ver
    2013
    Posts
    32

    Drop Down Data Validation

    Hi,

    I have an excel spreadsheet with 2 sheets. One has a drop down for State, then County, then City, then other information that is not important here.

    Sheet two has Data. The data has State in Column A, County in Column B and City in Column C.

    My drop down starts with selecting the State in Cell B3. C3 has the County. I want the drop down for County to only show the Counties that are listed in my data set for the state that is selected.

    Then I want the Cities drop down to only pull cities for cities within that county based on my data in sheet 2.

    Is there a way to do that? The counties and cities show up multiple times, so I only want the unique data to show in the drop down box.

    Your help is much appreciated!


    Thank you!

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Drop Down Data Validation

    See if you can adapt the attached to your needs. It uses named ranges for all 'sets" of data, and then uses INDIRECT to pull in those ranges
    Attached Files Attached Files
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Registered User
    Join Date
    12-09-2016
    Location
    Irvine, CA
    MS-Off Ver
    2013
    Posts
    32

    Re: Drop Down Data Validation

    Hi Ford,

    This does not work for my data set. There will be multiple instances of the same County and City in the data, so I need the data to be filtered for unique data and only show those unique strings without repeats. The formula you provided would have repeat data in the drop down box. Is there a way to only have the unique text show up?



    Thank you,

  4. #4
    Registered User
    Join Date
    12-09-2016
    Location
    Irvine, CA
    MS-Off Ver
    2013
    Posts
    32

    Re: Drop Down Data Validation

    Is the only way to have the drop down box show the unique values to use a formula to pull the unique values and then use that data for the drop down box?

    I have the following formula to filter through and pull unique data:

    Formula: copy to clipboard
    Please Login or Register  to view this content.

  5. #5
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Drop Down Data Validation

    Quote Originally Posted by bilich View Post
    Is the only way to have the drop down box show the unique values to use a formula to pull the unique values and then use that data for the drop down box?
    Yes




    --------------
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  6. #6
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Drop Down Data Validation

    Quote Originally Posted by bilich View Post
    Hi Ford,

    This does not work for my data set. There will be multiple instances of the same County and City in the data, so I need the data to be filtered for unique data and only show those unique strings without repeats. The formula you provided would have repeat data in the drop down box. Is there a way to only have the unique text show up?



    Thank you,
    To best describe or illustrate your problem you would be better off attaching a dummy workbook. The workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

    To attach a file to your post,
    click advanced (next to quick post),
    scroll down until you see "manage file",
    click that and select "add files" (top right corner).
    click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

    Once the upload is completed the file name will appear below the input boxes in this window.
    You can then close the window to return to the new post screen.

  7. #7
    Registered User
    Join Date
    12-09-2016
    Location
    Irvine, CA
    MS-Off Ver
    2013
    Posts
    32

    Re: Drop Down Data Validation

    Hi,

    Attached is a dummy Excel File. The data on it is small, but has enough for testing I believe.

    How it currently works is that the drop down references the Named Ranges that are off to the right in Sheet 1. I would like to have it just reference the data set on sheet 2, but I do not want any duplicates to show up. Are there any known ways to do that for a set of data like mine?


    Thank you,
    Attached Files Attached Files

  8. #8
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,007

    Re: Drop Down Data Validation

    See attached which creates two dynamic named ranges based on "State" and "County" selections. The selected State and county are named ranges "Select_State" and "Select_County"

    This code goes in Sheet1:

    Right click on tab then "View Code"

    Copy and paste code

    Please Login or Register  to view this content.
    This code goes in a general "Module"

    Alt+F11
    "Insert" ==>"Module"

    Copy and past code

    Please Login or Register  to view this content.
    Attached Files Attached Files

  9. #9
    Registered User
    Join Date
    12-09-2016
    Location
    Irvine, CA
    MS-Off Ver
    2013
    Posts
    32

    Re: Drop Down Data Validation

    Thank you John! That macro is perfect for my situation!

    I do have one question. I want to make sure there are no limits on the rows of data in Sheet 2. Is the line of code below the line that determines the amount of rows and therefore is dynamic and will adjust for the amount of data?


    Please Login or Register  to view this content.


    Thank you!!!

  10. #10
    Registered User
    Join Date
    12-09-2016
    Location
    Irvine, CA
    MS-Off Ver
    2013
    Posts
    32

    Re: Drop Down Data Validation

    Thank you John! That macro is perfect for my situation!

    I do have one question. I want to make sure there are no limits on the rows of data in Sheet 2. Is the line of code below the line that determines the amount of rows and therefore is dynamic and will adjust for the amount of data?


    Please Login or Register  to view this content.


    Thank you!!!

  11. #11
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,007

    Re: Drop Down Data Validation

    "lr" counts the rows in column A so it is be dynamic.

    The logic assumes that the data in Sheet2 is "sorted" i.e. City within County within State, as it is in your sample.

    You will see I added a few rows (for Florida) in my posted file.

  12. #12
    Registered User
    Join Date
    12-09-2016
    Location
    Irvine, CA
    MS-Off Ver
    2013
    Posts
    32

    Re: Drop Down Data Validation

    Thanks for pointing that out! I will make sure and keep like counties and cities together so the data shows up correctly.


    Thank you!!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Data validation drop down with ghost drop down
    By KEDENNIS in forum Excel General
    Replies: 11
    Last Post: 04-04-2016, 03:49 PM
  2. Replies: 4
    Last Post: 10-28-2015, 12:59 PM
  3. Drop down data validation
    By Manvinder8 in forum Excel General
    Replies: 2
    Last Post: 06-22-2010, 11:25 AM
  4. [SOLVED] Data validation, drop-down box
    By ahanmagey in forum Excel General
    Replies: 8
    Last Post: 10-07-2008, 09:29 AM
  5. Drop down data validation
    By Stephen Reid in forum Excel General
    Replies: 5
    Last Post: 07-27-2006, 08:05 AM
  6. [SOLVED] Data Validation - Drop Down
    By John in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-24-2006, 10:55 PM
  7. [SOLVED] Data Validation Drop-Down Will Not Appear
    By Joshua @ UDA in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 04-06-2006, 09:55 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1