+ Reply to Thread
Results 1 to 8 of 8

Dynamic Drop Down Lists (data validation)

  1. #1
    Registered User
    Join Date
    07-13-2018
    Location
    London
    MS-Off Ver
    Work: Excel 2016 (16.0.5005.1000 32bit) Home: Excel for M365 MSO (16.0.13001.20338 64bit)
    Posts
    19

    Dynamic Drop Down Lists (data validation)

    Hi All,

    I have need of a dynamic drop down list that is based off another table, the filtering for this is already provided for me by other columns, I just need to find a way of getting this to work (preferably on older versions of excell but from my searches and attempts at solving it I understand I may need to use newer functions such as textjoin).

    I've attached an example spreadsheet, on the sheet I'm actually working with there are around 6 option columns and will end up with thosands of rows, the table I'm drawing from has over a thousand rows however should generally resolve to a drop down list of anywhere between 0-40 options so I need a scalable solution if possible!

    Thanks in advance for any help you can give

    Edit: Updated file to include some other results.
    Attached Files Attached Files
    Last edited by Super_Bob; 08-02-2020 at 11:25 AM.

  2. #2
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    4,552

    Re: Dynamic Drop Down Lists (data validation)

    Please sort Sheet2 Column 1,2,3
    Then Data validation list at D2

    =OFFSET(Sheet2!$D$1,MATCH(A2&B2&C2,Sheet2!$A$2:$A$25&Sheet2!$B$2:$B$25&Sheet2!$C$2:$C$25,),,COUNTIFS(Sheet2!$A$2:$A$25,A2,Sheet2!$B$2:$B$25,B2,Sheet2!$C$2:$C$25,C2))
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    07-13-2018
    Location
    London
    MS-Off Ver
    Work: Excel 2016 (16.0.5005.1000 32bit) Home: Excel for M365 MSO (16.0.13001.20338 64bit)
    Posts
    19

    Re: Dynamic Drop Down Lists (data validation)

    Hi Bo_Ry,

    Thanks for having a look, for some reason the drop down lists for column d are not working on mine (office 365 subscription, validation is in there and the drop down icon shows but no options appear), do you know why that might be?

    I see where you've gone with it though as basically you are creating a siding reference box for the list, I'll see if I can adapt it to my main sheet, thank you!

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    42,129

    Re: Dynamic Drop Down Lists (data validation)

    @Super_Bob

    Please update your forum profile to Office 365 ('current' is not specific enough, as there are more than one 'current' versions). Thanks.

    The drop-down is non-functional here, too.
    Last edited by AliGW; 08-02-2020 at 12:22 PM.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  5. #5
    Registered User
    Join Date
    07-13-2018
    Location
    London
    MS-Off Ver
    Work: Excel 2016 (16.0.5005.1000 32bit) Home: Excel for M365 MSO (16.0.13001.20338 64bit)
    Posts
    19

    Re: Dynamic Drop Down Lists (data validation)

    Hi AliGW,

    I've updated my profile with my home and work versions now.

  6. #6
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    42,129

    Re: Dynamic Drop Down Lists (data validation)

    Very thorough! Thanks - it's helpful to know.

  7. #7
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    4,552

    Re: Dynamic Drop Down Lists (data validation)

    Please try again

    =OFFSET(Sheet2!$D$1,MATCH(A2&B2&C2,INDEX(Sheet2!$A$2:$A$25&Sheet2!$B$2:$B$25&Sheet2!$C$2:$C$25,),),,COUNTIFS(Sheet2!$A$2:$A$25,A2,Sheet2!$B$2:$B$25,B2,Sheet2!$C$2:$C$25,C2))
    Attached Files Attached Files

  8. #8
    Registered User
    Join Date
    07-13-2018
    Location
    London
    MS-Off Ver
    Work: Excel 2016 (16.0.5005.1000 32bit) Home: Excel for M365 MSO (16.0.13001.20338 64bit)
    Posts
    19

    Re: Dynamic Drop Down Lists (data validation)

    Hi Bo_Ry,

    The new version worked perfectly thank you! Also I've just been adapting it for my actual work sheet and your old version worked a treat (something to do with the office version on my work computer maybe), I did have to shorten the references by creating named areas on the second sheet as there seems to be a 256 char limit on the data validation field but that was easy enough!

    Thank you for all the help

+ 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. VBA for Dynamic Dependent Drop Down Data Validation Lists
    By benjhardie in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-12-2019, 03:32 AM
  2. [SOLVED] Help with Dynamic Validation Lists / Drop Downs / dynamic filters
    By rjnc in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 05-22-2016, 08:10 PM
  3. [SOLVED] data validation lists - drop down lists too small
    By reglook0736 in forum Excel General
    Replies: 3
    Last Post: 04-24-2015, 12:49 PM
  4. Dynamic data validation lists
    By FlashyChap in forum Excel General
    Replies: 2
    Last Post: 01-12-2015, 11:31 AM
  5. Dynamic Data Validation Lists
    By Daniel Brown in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-05-2014, 08:08 AM
  6. [SOLVED] need a dynamic source for data validation drop down lists
    By dredwolf in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 10-29-2012, 11:01 PM

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