+ Reply to Thread
Results 1 to 17 of 17

Dynamic Drop Down with Multiple Criteria

  1. #1
    Registered User
    Join Date
    08-15-2019
    Location
    Dallas, TX
    MS-Off Ver
    Office 365
    Posts
    11

    Dynamic Drop Down with Multiple Criteria

    I am trying to create a time tracking sheet. We want the user to be able to select project type, category, and sub-category. This is for an audit group.

    We would want the time sheet to have 3 drop down lists to mirror the options available. The trick is that we are trying to make each dynamic based on the columns selected. I've found some examples using named ranges, but it looks like to accomplish the categories we have in out sheet I would have to make a lot of named ranges and that could be difficult to understand and manage for someone else.

    I would think you'd be able to create type of dynamic match that you use to create a data validation list, but I'm really not sure exactly how to do that.

    Any ideas?

    At a high level the category tab looks like this

    I've attached a file the one tab houses our Project Type, Category, and Sub-Category. The next types has a timesheet which we would like to have 3 dynamic drop down lists and a calendar to the right.

    Let me know if I need to be more clear.


    Open Excel Online Link if you don't want to download:

    1drv.ms/x/s!AnMRr0Q8-EGOgqxY2-MsE94uXSj6cw?e=lAuR5n
    Attached Files Attached Files

  2. #2
    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
    41,412

    Re: Dynamic Drop Down with Multiple Criteria

    This has been asked hundreds of times before, and if you search the forum, you'll find many threads on it.

    Here's one for starters: https://www.excelforum.com/excel-gen...on-a-list.html

    And here's a search result: https://www.excelforum.com/search.php?searchid=9887889
    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.

  3. #3
    Registered User
    Join Date
    08-15-2019
    Location
    Dallas, TX
    MS-Off Ver
    Office 365
    Posts
    11

    Re: Dynamic Drop Down with Multiple Criteria

    It looks like these examples primarily use named ranges with two criteria. I'm specifically trying to avoid that since that means I have to manage approx 170 named ranges to make mine work like the examples because that's the number of categories I have for sub-categories. There really isn't a way for excel for a formula to evaluate matches in a table without setting up a named range for each higher level category.

  4. #4
    Forum Guru Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    It changes constantly, but based in Ireland.
    MS-Off Ver
    Excel 2013 & 2016
    Posts
    24,631

    Re: Dynamic Drop Down with Multiple Criteria

    Yes there is!! It will take me a while...
    Glenn



  5. #5
    Registered User
    Join Date
    08-15-2019
    Location
    Dallas, TX
    MS-Off Ver
    Office 365
    Posts
    11

    Re: Dynamic Drop Down with Multiple Criteria

    Awesome Glen!

  6. #6
    Valued Forum Contributor
    Join Date
    02-02-2016
    Location
    Indonesia
    MS-Off Ver
    Office 365
    Posts
    791

    Re: Dynamic Drop Down with Multiple Criteria

    I have an example of 2 dependent data validation. It's using VBA, need 1 helper column & only 1 named range.
    If you're interested in this approach I can set up for 3 data validation as you need.
    Here's an example:

  7. #7
    Forum Guru Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    It changes constantly, but based in Ireland.
    MS-Off Ver
    Excel 2013 & 2016
    Posts
    24,631

    Re: Dynamic Drop Down with Multiple Criteria

    Hi. Normally, this approach requires 5 helper column, but just one named range per level (so... 3 in your case). However... Your raw data were messy. There are duplicates all the way down the list (see project tracking rows 13/14, 17/18, etc). getting rid of those, needed a few more helpers. There is a huge amount going on here. I do not propose to explain ANYTHING until you have had a long hard look at it, and have confirmed that it is what you need. Then we can take it in small steps!!
    Attached Files Attached Files

  8. #8
    Registered User
    Join Date
    08-15-2019
    Location
    Dallas, TX
    MS-Off Ver
    Office 365
    Posts
    11

    Re: Dynamic Drop Down with Multiple Criteria

    Glen, I'm busy the next few hours, but I'll take a look at it. Thanks

  9. #9
    Forum Guru Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    It changes constantly, but based in Ireland.
    MS-Off Ver
    Excel 2013 & 2016
    Posts
    24,631

    Re: Dynamic Drop Down with Multiple Criteria

    Take your time... I don't look forward to explaining it all! I'm quite a few hours ahead of you, and may be on the road for at least Friday and possibly Saturday..

  10. #10
    Forum Expert
    Join Date
    12-11-2011
    Location
    Netherlands
    MS-Off Ver
    office 365
    Posts
    2,878

    Re: Dynamic Drop Down with Multiple Criteria

    Maybe is this another option with less duplicate data.
    Then you need only 2 short helper columns
    Attached Files Attached Files
    Willem
    English is not my native language sorry for errors
    Please correct me if I'm completely wrong

  11. #11
    Registered User
    Join Date
    08-15-2019
    Location
    Dallas, TX
    MS-Off Ver
    Office 365
    Posts
    11

    Re: Dynamic Drop Down with Multiple Criteria

    popipipo, unfortunately this won't work like I would like, because my subcategories will change. I know my example file wasn't great because I only had the 3 type, but I need to be able to have add a new unique set of 3 values and the data validation list will update for it.

  12. #12
    Registered User
    Join Date
    08-15-2019
    Location
    Dallas, TX
    MS-Off Ver
    Office 365
    Posts
    11

    Re: Dynamic Drop Down with Multiple Criteria

    Glen the formulas in the helper columns and the formula in the data validations lists are really hard for me to understand.

    I did try to make it work like I thought it was intended. I changed on of the existing subcategories and it. I also added a new combination of 3 values and extended the helper columns. This one worked as expected for Type, Category, but didn't show the subcategory.

    I've attached mine that I changed. I also created a tab called "What I Changed" so you can see what I changed and what I was seeing.
    Attached Files Attached Files

  13. #13
    Forum Guru Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    It changes constantly, but based in Ireland.
    MS-Off Ver
    Excel 2013 & 2016
    Posts
    24,631

    Re: Dynamic Drop Down with Multiple Criteria

    I will repost it in a few hours.

  14. #14
    Valued Forum Contributor
    Join Date
    02-02-2016
    Location
    Indonesia
    MS-Off Ver
    Office 365
    Posts
    791

    Re: Dynamic Drop Down with Multiple Criteria

    Here's an example of 3 dependent data validation. It's using VBA (not formula), need 1 helper column & only 1 named range.
    The code is rather long but is pretty easy set it up, even if you don't understand the code.
    Here's the sample workbook & your workbook using this set up.

  15. #15
    Forum Guru Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    It changes constantly, but based in Ireland.
    MS-Off Ver
    Excel 2013 & 2016
    Posts
    24,631

    Re: Dynamic Drop Down with Multiple Criteria

    Thank you for your careful notes on what you did and what went wrong. It made it so easy to spot my mistake. You should have been a scientist... you seem to have a very logical mind. Enough nice words!!

    So, I'm back again. I had a 3-level DD on my PC's HDD - which I used for your problem - but it was set up for no duplicates in the tertiary level. I was in a bit of a rush, and must have been suffering from coffee deprivation, and simply goofed up.

    So, simpler, easier now. One Table (why did I create 2???), with helpers laid out in a way that should make it a bit clearer what each one is helping to do. In effect, as long as the table range includes all your values, it should be fully dynamic. It's aided by 3 Named Ranges (CTRL-F3 to view/edit) which dynamically select the correct cell range, depending on your previous choice(s).

    Do your best to break it!! Once again happy to fix any other goof-ups and explain as needed.
    Attached Files Attached Files

  16. #16
    Registered User
    Join Date
    08-15-2019
    Location
    Dallas, TX
    MS-Off Ver
    Office 365
    Posts
    11

    Re: Dynamic Drop Down with Multiple Criteria

    This is wonderful. Thank you so much!

  17. #17
    Forum Guru Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    It changes constantly, but based in Ireland.
    MS-Off Ver
    Excel 2013 & 2016
    Posts
    24,631

    Re: Dynamic Drop Down with Multiple Criteria

    You're welcome.



    If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

    It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.

+ 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. Dynamic drop-down, based on previous drop-down criteria
    By Wardinho in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 12-19-2017, 11:00 AM
  2. Replies: 3
    Last Post: 06-29-2017, 04:19 PM
  3. Formula for summing on multiple row criteria and a dynamic column criteria
    By ianswilson815 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-25-2016, 01:58 PM
  4. Dynamic drop down list, based on a criteria
    By marcinpec in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 07-17-2014, 11:00 AM
  5. [SOLVED] Show data with month criteria drop list (dynamic)
    By Jhon Mustofa in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 03-16-2014, 12:54 PM
  6. create a dynamic drop down list based on specific criteria
    By stevegrobertson in forum Excel General
    Replies: 4
    Last Post: 02-21-2012, 08:07 PM
  7. Dynamic Drop Down List...with Criteria
    By TimE in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 07-17-2008, 11:43 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