+ Reply to Thread
Results 1 to 3 of 3

Using a list to validate options on another list

  1. #1
    Registered User
    Join Date
    03-08-2015
    Location
    UK
    MS-Off Ver
    2013
    Posts
    33

    Question Using a list to validate options on another list

    In my spreadsheet I have two options and you select them via a drop down box. Is it possible to select an option on option 1 which them determines which options in "option 2" are available to you?

    E.g. if you select A in the first instance then only 1 and 2 should appear as options in the second box, B only shows 3 and 4, C show 5 and 6, and D shows 7 to 10. Or if not someway of communicating that your choice is unacceptable, i.e. choosing A and 3 whould display a message in the adjacent box saying this combination was not valid.

    Many thanks in advance
    Attached Files Attached Files

  2. #2
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    2013 / 2016 / 365
    Posts
    7,081

    Re: Using a list to validate options on another list

    What you seek is called a cascading drop-down list. Do a web search for it. The method you are most likely to find involves setting up the lists you want for the first selection across the columns and the items for the selection in the rows under it, and then using MATCH and OFFSET.

    I have a somewhat different approach which can cascade any number of levels. If the data in the lower levels of the cascade are unique, you can use the non-vba method. Otherwise, the vba method should work in all cases.
    http://www.utteraccess.com/wiki/Casc..._%28Non-VBA%29 (Non VBA)
    http://www.utteraccess.com/wiki/Casc...ists_%28VBA%29

    However, there may be an even simpler solution: look up Slicers. They are easy to implement, and they are self-cascading.

    Here is an example using slicers. The data in the table in columns A & B set the "rules." I made pivot tables against this table with the filters being First Choice and Second Choice. This isn't necessary, but if you want to use the selection anywhere, there's a cell that you can reference.

    So do an example: Select A from the First Choice Slicer. Notice that the second choice slicer has some selections already grayed out and that Cell F1 shows the selection. Select one of the available choices and it will appear in cell F4.

    Clear both slicers by clicking on the funnel icon on the upper right. The pivot table selections revert to (All).

    Now select 3 as the second choice. 3 is set in Cell F4. Note now that only B is available in the first choice slicer. This is because ONLY B has a second choice equal to 3.

    Slicers work both ways.
    Attached Files Attached Files
    One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.

    A picture is worth a thousand words, but a sample spreadsheet is more likely to be worked on.

  3. #3
    Registered User
    Join Date
    03-08-2015
    Location
    UK
    MS-Off Ver
    2013
    Posts
    33

    Re: Using a list to validate options on another list

    Thats great, many thanks for that.

+ 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. Print all options from dropdown list to PDF and name the files the exact names in the list
    By johnwilliamboyle in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-17-2014, 11:49 AM
  2. Replies: 2
    Last Post: 08-05-2013, 10:08 PM
  3. Replies: 6
    Last Post: 06-05-2009, 12:03 PM
  4. [SOLVED] My Excel drop-down list eliminates from list options chosen. Help
    By Sybil in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-19-2006, 05:25 PM
  5. validation list with drop down list of options??
    By luke013 in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 06:05 PM
  6. validation list with drop down list of options??
    By Duke Carey in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 09-06-2005, 05:05 AM
  7. validation list with drop down list of options??
    By luke013 in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-05-2005, 10:05 PM

Tags for this Thread

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