+ Reply to Thread
Results 1 to 5 of 5

Dynamic Dual-Dependent Dropdowns?

  1. #1
    Forum Contributor
    Join Date
    03-26-2015
    Location
    Cookeville, TN
    MS-Off Ver
    Excel 2007
    Posts
    200

    Dynamic Dual-Dependent Dropdowns?

    Is there any way to create a dynamic, dual-dependent dropdown list? See attached example file. It has a list of grocery products, each having a Department, Category, and Item (such as Dairy, Yogurt, and Dannon blueberry yogurt 4 oz. 4-pack). I'd like to create a dropdown list that shows the list of Items associated with a chosen Department and Category, where the dropdown list reflects any changes in the base list.

    I tried setting up some simple array formulas to extract the selected Items (cells K8 and K12). The first version [using IF(AND] doesn't work (I'm not sure why?). But no matter; I added a helper column "Combo" that combines the Department and Category, and my second formula (cell K12) works, in that it does find the matching Items. But it also (of course) has a FALSE wherever a match does not occur.
    So Question 1: Is there any way to get rid of the FALSE entries and return (via the array formula) only the matching Items?

    I then tried to set up data validation (list) in cell K4. I discovered that you apparently can't enter an array formula (copying the formula in K12) into a list validation source. So I tried simply referencing cell K12. That gives me a single-entry list (only the first entry).
    So Question 2: If I could create an array formula to extract only the matching Items, is there any way to (a) use that formula directly for data validation, or (b) reference that entire list for data validation?

  2. #2
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,810

    Re: Dynamic Dual-Dependent Dropdowns?

    I'm a little confused, but I think you may be looking for cascading combo boxes. Here is a link to help you there.

    https://www.thesmallman.com/cascading-drop-down-lists
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  3. #3
    Forum Contributor
    Join Date
    03-26-2015
    Location
    Cookeville, TN
    MS-Off Ver
    Excel 2007
    Posts
    200

    Re: Dynamic Dual-Dependent Dropdowns?

    The cascading combo boxes is a great idea, but in this instance it's not quite what I'm looking for. I would actually have a whole grid of these item selection cells (not just a single one), where each row relates to a certain Department and each column relates to a certain Category (and the table is then a whole grid where a user can select an Item from a Department and Category by choosing it via the dropdown at the intersection of the chosen row and column). So each dropdown in the grid will reference the Dept and Category for its own row and column. So using VBA and combo boxes is kind of impractical. I'd like a single formula / solution that can be used in every cell within the grid.
    And the data will be set up in an unordered way (as in the example file), so using named ranges won't work either.
    But I still really appreciate the suggestion!

  4. #4
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Dynamic Dual-Dependent Dropdowns?

    Try the attached.
    1) To ThisWorkbook code module
    Please Login or Register  to view this content.
    2) To Sheet1 code module
    Please Login or Register  to view this content.

  5. #5
    Forum Contributor
    Join Date
    03-26-2015
    Location
    Cookeville, TN
    MS-Off Ver
    Excel 2007
    Posts
    200

    Re: Dynamic Dual-Dependent Dropdowns?

    Thanks for that lengthy response, but I don't want to use VBA for this.
    Guess I posted this in the wrong forum - should have put it in Formulas and Functions instead! My mistake...

+ 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. multiple dynamic dependent dropdowns
    By 7WiZARD in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 07-01-2016, 06:51 AM
  2. [SOLVED] Dependent Dropdowns
    By Meabh in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-29-2015, 08:11 AM
  3. Dynamic Dependent Dropdowns possible without naming ranges?
    By elmorekevin in forum Excel General
    Replies: 1
    Last Post: 10-15-2014, 03:23 AM
  4. Help with Dependent Dropdowns
    By brentlindeman in forum Excel General
    Replies: 1
    Last Post: 01-28-2014, 07:30 PM
  5. [SOLVED] Dependent Dropdowns, clearing dependent value when parent value changes
    By ptmald in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-14-2013, 01:52 PM
  6. Dependent dropdowns
    By Jogier505 in forum Excel General
    Replies: 5
    Last Post: 12-30-2009, 08:39 PM
  7. [SOLVED] 3+ Dynamic Dependent dropdowns?
    By KD in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-20-2006, 01:15 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