+ Reply to Thread
Results 1 to 11 of 11

Drop Down list dependent on 2 previous drop downs

  1. 11-12-2018, 01:26 PM #1
    mrkawht
    mrkawht is offline
    Registered User
    Join Date
    11-12-2018
    Location
    Tulsa, OK
    MS-Off Ver
    2016
    Posts
    10

    Drop Down list dependent on 2 previous drop downs

    I am trying to create a drop down list (without duplicates) that is based on 2 previous choices. Details of what is in workbook:

    - Report tab is where everything is shown
    - Dropdown tab is currently just a list of Product Names
    - Spec Data tab is where all the data for the Report is pulled from

    I have 2 named tables in the Name Manager for the drop downs for Spec Type and Test.

    Spec_Type:
    =OFFSET('Spec Data'!$A$1,MATCH(Report!$A3,'Spec Data'!$A:$A,0)-1,1,COUNTIF('Spec Data'!$A:$A,Report!$A3),1)

    Test:
    =OFFSET('Spec Data'!$B$1,MATCH(Report!$B3,'Spec Data'!$B:$B,0)-1,1,COUNTIF('Spec Data'!$B:$B,Report!$B3),1)

    Problems I have:
    - Both of these drop downs contain duplicates
    - Test drop down is only based on Spec Type
    - For instance Product=1, Spec Type=A, Test=Test4 does not exist, but Test4 does is exist for Product=1, Spec-Type=B
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 11-12-2018, 03:47 PM #2
    tim201110
    tim201110 is offline
    Forum Expert tim201110's Avatar
    Join Date
    10-23-2011
    Location
    Russia
    MS-Off Ver
    2016, 2019
    Posts
    2,357

    Re: Drop Down list dependent on 2 previous drop downs

    with formulas
    made for product 1
    the rest is similarly
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  3. 11-12-2018, 06:33 PM #3
    mehmetcik
    mehmetcik is offline
    Forum Expert
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,448

    Re: Drop Down list dependent on 2 previous drop downs

    Now you are asking.

    On your Dropdown Sheet

    Cell A1 = Product
    Enter this formula in A2 using Ctrl Shift Enter and fill down
    =IFERROR(INDEX(Prod,MATCH(0,COUNTIF(Prod,"<"&Prod)-SUM(COUNTIF(Prod,A$1:A1)),0)),"")

    Cell B2 = Spec Type
    Enter this formula in B2 using Ctrl Shift Enter and fill down
    =IFERROR(INDEX(OFFSET(Prod,0,1),MATCH(0,COUNTIF(OFFSET(Prod,0,1),"<"&OFFSET(Prod,0,1))-SUM(COUNTIF(OFFSET(Prod,0,1),B$1:B1)),0)),"")

    Cell C2 = Test
    Enter this formula in C2 using Ctrl Shift Enter and fill down
    =IF(ROW()>SUM(IF(Report!A$1&Report!B$1='Spec Data'!$A$1:$A$148&'Spec Data'!$B$1:$B$148,1,0))+1,"",INDEX('Spec Data'!$C$1:$C$148,SMALL(IF(Report!A$1&Report!B$1='Spec Data'!$A$1:$A$148&'Spec Data'!$B$1:$B$148,ROW('Spec Data'!$B$1:$B$148),0),ROW()-1+SUM(IF(Report!A$1&Report!B$1<>'Spec Data'!$A$1:$A$148&'Spec Data'!$B$1:$B$148,1,0)))))

    Define these Names using the formulas

    Prod ='Spec Data'!$A$2:INDEX('Spec Data'!$A$1:$A$200,MATCH(9^99,'Spec Data'!$A$1:$A$200),1)
    Prod2 =Dropdown!$A$2:INDEX(Dropdown!$A$1:$A$200,MATCH(9^99,Dropdown!$A$1:$A$200),1)
    Spec =Dropdown!$B$2:INDEX(Dropdown!$B$1:$B$200,MATCH("zzzzzzzzzzzzz",Dropdown!$B$1:$B$200),1)
    Test =Dropdown!$C$2:INDEX(Dropdown!$C$1:$C$200,MATCH("zzzzzzzzzzzzz",Dropdown!$C$1:$C$200),1)

    Use Prod2, Spec and Test as your drop down lists.
    Attached Files Attached Files
    Last edited by mehmetcik; 11-12-2018 at 06:35 PM.
    My General Rules if you want my help. Not aimed at any person in particular:

    1. Please Make Requests not demands, none of us get paid here.

    2. Check back on your post regularly. I will not return to a post after 4 days.
    If it is not important to you then it definitely is not important to me.
    Reply With QuoteRegister To Reply
  4. 11-13-2018, 12:53 PM #4
    mrkawht
    mrkawht is offline
    Registered User
    Join Date
    11-12-2018
    Location
    Tulsa, OK
    MS-Off Ver
    2016
    Posts
    10

    Re: Drop Down list dependent on 2 previous drop downs

    Thanks for all the help.

    tim201110 - Yours seems to work, but the test data represents only about 1% of the total data I have. The calculations take way too long (hours) and make the final workbook too large to manage.

    mehmetcik - Does the fact that my real data doesn't have a number for the product name affect how the calculation works? I currently can't get it to work with my real data.
    Reply With QuoteRegister To Reply
  5. 11-13-2018, 01:04 PM #5
    Glenn Kennedy
    Glenn Kennedy is offline
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    44,004

    Re: Drop Down list dependent on 2 previous drop downs

    I think that we need some indication of scale here. Roughly... many products / spec types / tests are there?

    Also: How many rows (MAX) do you want to have these cascading dropdowns in? 1, 5, 50, 500????
    Glenn



    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
    Reply With QuoteRegister To Reply
  6. 11-13-2018, 01:45 PM #6
    mrkawht
    mrkawht is offline
    Registered User
    Join Date
    11-12-2018
    Location
    Tulsa, OK
    MS-Off Ver
    2016
    Posts
    10

    Re: Drop Down list dependent on 2 previous drop downs

    About 300 products, 4-10 spec types per product, and 8-20 tests per spec type. Overall about 12000 lines of data in Spec Data.
    Reply With QuoteRegister To Reply
  7. 11-13-2018, 01:50 PM #7
    Glenn Kennedy
    Glenn Kennedy is offline
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    44,004

    Re: Drop Down list dependent on 2 previous drop downs

    OMG!!! So before I fall over how many rows of cascading dds do you need? I think this is going to be impossible with formulae, unless you only want a very small number of rows with dds in them
    Reply With QuoteRegister To Reply
  8. 11-13-2018, 02:06 PM #8
    mehmetcik
    mehmetcik is offline
    Forum Expert
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,448

    Re: Drop Down list dependent on 2 previous drop downs

    Change this line

    Please Login or Register  to view this content.
    To

    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  9. 11-13-2018, 02:47 PM #9
    mrkawht
    mrkawht is offline
    Registered User
    Join Date
    11-12-2018
    Location
    Tulsa, OK
    MS-Off Ver
    2016
    Posts
    10

    Re: Drop Down list dependent on 2 previous drop downs

    Thanks mehmetcik I'll give it a try.

    Glenn - Only need about 15 rows on the report when its all said and down.
    Reply With QuoteRegister To Reply
  10. 11-13-2018, 03:23 PM #10
    tim201110
    tim201110 is offline
    Forum Expert tim201110's Avatar
    Join Date
    10-23-2011
    Location
    Russia
    MS-Off Ver
    2016, 2019
    Posts
    2,357

    Re: Drop Down list dependent on 2 previous drop downs

    The calculations take way too long (hours)
    i don't think so
    it calculated once
    only formulas for dropdowns are quite complicated


    not so bad
    just pull down a row as far as you have products
    Attached Files Attached Files
    Last edited by tim201110; 11-13-2018 at 04:46 PM.
    Reply With QuoteRegister To Reply
  11. 11-16-2018, 02:39 PM #11
    mrkawht
    mrkawht is offline
    Registered User
    Join Date
    11-12-2018
    Location
    Tulsa, OK
    MS-Off Ver
    2016
    Posts
    10

    Re: Drop Down list dependent on 2 previous drop downs

    Thanks again for all the help. Both of these options worked once the index functions finally completed and the dropdown tables were created. The amount of data I have what makes it take so long (and maybe slow work laptop too).

    I think I need to convince everyone that Access is their friend and Excel isn't the best way to handle this much information.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Drop down dependent on previous filtered list - NOT SORTED ALPHABETICALLY
    By K Cooper in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-25-2018, 06:38 PM
  2. Drop Down List Dependent on Previous Drop Down List
    By matt87 in forum Excel General
    Replies: 2
    Last Post: 02-22-2018, 12:54 PM
  3. [SOLVED] Dependent Drop-Downs
    By Rustico18 in forum Excel General
    Replies: 5
    Last Post: 03-09-2017, 12:14 AM
  4. Initial default drop downs and dependent default drop downs
    By hudsone777 in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 12-07-2016, 07:05 PM
  5. Drop down box dependent on previous drop down box input
    By Beltzer71 in forum Excel General
    Replies: 2
    Last Post: 09-29-2011, 06:12 AM
  6. Auto-update initial drop-down selection based on previous drop-downs
    By thornomad in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 06-16-2010, 09:55 AM
  7. Dependent drop downs.
    By Mattypb in forum Excel - New Users/Basics
    Replies: 2
    Last Post: 11-16-2009, 08:47 AM

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1