+ Reply to Thread
Results 1 to 7 of 7

Dependent drop-down lists based off table

  1. #1
    Registered User
    Join Date
    12-31-2024
    Location
    UK
    MS-Off Ver
    Excel 2016
    Posts
    4

    Question Dependent drop-down lists based off table

    Hi all,

    I have a spreadsheet that looks a little like this:

    Header 1 Header 2 Header 3 Header 4
    X A 1 x
    X A 1 y
    X A 2 x
    X A 2 y
    X B 1 x
    X B 1 y
    Y A 1 x

    I'm looking to do some funky data validation where there is a drop-down list for each header, and in each of those drop-down lists are the unique values in that header's column, but where each list is only those values that occur based on the previous list.

    So for the above example, list 1 is 'X or Y'; select X, then list 2 becomes 'A or B'; select A and list 3 is '1 or 2' - if you select B list 3 is '1' only. And so on...

    Now I can do this with a whole load of manual name management, creation of tables which only have unique values etc. - but in my actual spreadsheet, I've got about 160 unique values for Header 3 and 7,500 for Header 4, so that's looking like a daunting task.

    So I'm hoping you fine folks on this forum will be able to help - is it possible to make dependent drop-down lists based off a single table with only unique values shown?

    I'm on excel 2016, so don't have access to the newest functions, but will happily use Macros if that makes life simpler.

    Many thanks!

    T.

    Edit: added an attached sample spreadsheet - the table is what I'm looking to validate, and I've put in example boxes showing a possible flow through the 4 drop-down lists.
    Attached Files Attached Files
    Last edited by tflats; 12-31-2024 at 10:31 AM. Reason: Adding Attachment File

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (both in England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2507 (Windows 11 Home 24H2 64-bit)
    Posts
    91,665

    Re: Dependent drop-down lists based off table

    Welcome to the forum.

    There are instructions at the top of the page explaining how to attach your sample workbook (yellow banner: HOW TO ATTACH YOUR SAMPLE WORKBOOK). Screenshots are of little practical use as we cannot manipulate them.

    A good sample workbook has just 10-20 rows of representative data that has been desensitised. The sample layout accurately matches that of your real data. It also has expected results mocked up, worked examples where required, relevant cells highlighted and a few explanatory notes.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help. It's a universal courtesy.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    NB:
    as a Moderator, I never accept friendship requests.
    Forum Rules (updated August 2023): please read them here.

  3. #3
    Registered User
    Join Date
    12-31-2024
    Location
    UK
    MS-Off Ver
    Excel 2016
    Posts
    4

    Re: Dependent drop-down lists based off table

    Ah was hoping my picture would be enough - added one now Ali, thanks.

  4. #4
    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
    31,120

    Re: Dependent drop-down lists based off table

    Macro in sheet "Data": right-click on tab and view code

    Please Login or Register  to view this content.
    in Standard module

    Please Login or Register  to view this content.

    "Location" is named range "Location_DV": "Person/Cars" DVs are generated by code

    Columns L:O is filtered list based on DV selection
    Attached Files Attached Files
    Last edited by JohnTopley; 01-01-2025 at 08:18 AM.
    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

  5. #5
    Registered User
    Join Date
    12-31-2024
    Location
    UK
    MS-Off Ver
    Excel 2016
    Posts
    4

    Re: Dependent drop-down lists based off table

    Thanks for that John.

    I'm going to leave this open for now, just in case anyone knows of a way without macros, but if not I'll have a play with your code and consider it solved, thank you.

  6. #6
    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
    31,120

    Re: Dependent drop-down lists based off table

    See attached which a formula-based solution

    in R3

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


    in S3

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


    Enter with Ctrl+Shift+Enter (CSE)

    in T3

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


    Enter with Ctrl+Shift+Enter (CSE)

    All the above are dynamic named ranges.

    I have left the VBA which generates the filtered data: if not required, then delete code from sheet "Data".
    Attached Files Attached Files
    Last edited by JohnTopley; 01-02-2025 at 07:58 AM.

  7. #7
    Registered User
    Join Date
    12-31-2024
    Location
    UK
    MS-Off Ver
    Excel 2016
    Posts
    4

    Re: Dependent drop-down lists based off table

    Amazing, thank you John! Sorry for the late reply, but this is spot on, thanks

+ 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. Populating cells based on dependent drop down lists within a table
    By heapy41 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 05-03-2021, 04:01 PM
  2. [SOLVED] Dependent Drop Down Lists Based On A Cell Value
    By Jojothemonk3y in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-27-2019, 08:01 AM
  3. [SOLVED] Clear up to three dependent drop down lists based on selection in superior drop down list
    By theletterh in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 03-04-2016, 09:31 PM
  4. Filtering based on values in 3 dependent drop down lists
    By fmatre in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-06-2014, 10:31 AM
  5. Dependent Drop Down Lists with Lookup in Table Formula
    By dannysporea in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 10-30-2013, 12:27 PM
  6. [SOLVED] Return value based on 2 dependent drop down lists
    By arsene2conde in forum Excel Formulas & Functions
    Replies: 19
    Last Post: 10-14-2013, 09:44 AM
  7. IF THEN ELSE based on Three Dependent Drop Down Lists (VBA)
    By vtsoldier2010 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-15-2012, 09:56 AM

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