+ Reply to Thread
Results 1 to 7 of 7

Drop down lists dependent on 2 prior selections

  1. #1
    Registered User
    Join Date
    12-02-2013
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    7

    Question Drop down lists dependent on 2 prior selections

    Hello All,

    This is one of my first posts anywhere for anything. I've always been the type to research, Google and experiment to find answers, but this time I'm stumped...

    I am trying to make dependent drop down lists which depend on 2 previous selections. I can't set up cascading dependencies (such as Country generates State selection which generates City) because I am working with a manufacturing process.

    Example: Product A consists of 3 process steps: Assembly, Test, Verify. The selections for each step are unique and unique to Product A
    Product B consists of 3 process steps: Assembly, Test, Verify. The selections for each step are unique and unique to Product B

    I could create 3 worksheets (Assembly, Test and Verify) to remove a dependency, but that would make some of the later data collection more difficult. I want to be able to select the product and the product step to get a drop down specific to that action on that product, all in a single list. Is there a way to do this without VBA? I am not married to drop down lists and am open to other solutions.

    Thanks.

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,603

    Re: Drop down lists dependent on 2 prior selections

    Take a look at Debra Dalgleish's website here:

    http://www.contextures.com/xlDataVal02.html

    for in-depth tutorials about dependent data validation.

    Hope this helps.

    Pete

  3. #3
    Registered User
    Join Date
    12-02-2013
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: Drop down lists dependent on 2 prior selections

    I found a post suggesting the use of =INDIRECT(SUBSTITUTE(B2&C2," ","")). I believe this will work. I am willing to make another 15 or so duplicate lists if I need to, but that could become a maintenance nightmare... I have several different products that I would like to have reference the same list. Example: Products A-F all have the same drop down selection for the Test step. Using Substitute as it is presented requires a seperate list for each product. Is there a way to incorporate an IF selection or similar so that Products A-F all will all pull up the same drop down when Assembly is chosen?

  4. #4
    Registered User
    Join Date
    12-02-2013
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: Drop down lists dependent on 2 prior selections

    That is the posting I was referring to. I posted before seeing your reply.

  5. #5
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,603

    Re: Drop down lists dependent on 2 prior selections

    That's joining B2 and C2 together, so it will give you unique names. However, you can do this:

    =INDIRECT(SUBSTITUTE(IF(C2="Test","",B2)&C2," ",""))

    so that the named range Test will not be unique for the first choice code. You can use Test1, Test2 etc. for those that are unique.

    Hope this helps.

    Pete

  6. #6
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,578

    Re: Drop down lists dependent on 2 prior selections

    in the attached example, I did not use additional defined names, but used the result of the intersection of products versus process step in an INDEX in a Data Validation List.

    So D9 Data validation

    =INDEX(Dataset, , MATCH(INDEX($B$2:$D$4, MATCH($B$9, $A$2:$A$4,0), MATCH($C$9, $B$1:$D$1,0)), $F$1:$N$1))

    Then if certain products share certain process steps (in my example, Product A and C both have the same steps for "Test", there's no duplication of effort.
    Attached Files Attached Files
    ChemistB
    My 2?

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  7. #7
    Registered User
    Join Date
    12-02-2013
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: Drop down lists dependent on 2 prior selections

    Thank you Pete and Chemist B. Pete's answer is exactly what I was looking for. However, using Chemist's INDEX MATCH method opens up a level of precision and flexability that I previously thought was impractical. I'll spend some time understanding INDEX and MATCH in more detail... my first learning challenge is going to be to understand and fix the supplied function or my implementation so that it doesn't return "#VALUE" when dropped in the supplied spread sheet. Thank you both for your 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. output dependent on selections from 2 drop down lists
    By Mlacour in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-29-2013, 12:08 PM
  2. Replies: 1
    Last Post: 09-25-2013, 02:35 PM
  3. Dependent drop down lists without creating unique named lists
    By pajordan in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-11-2013, 12:20 PM
  4. Dependent Lists - Limit selections based on used values
    By jonmadjon in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-14-2013, 09:06 AM
  5. Combining selections from drop-down lists into a separate drop-down list
    By SCIFINUT in forum Excel Formulas & Functions
    Replies: 13
    Last Post: 02-27-2012, 05:41 PM
  6. Replies: 9
    Last Post: 01-03-2012, 03:00 PM
  7. Replies: 1
    Last Post: 04-11-2006, 12:30 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