+ Reply to Thread
Results 1 to 7 of 7

Developing Unique Dependent Dropdowns

  1. #1
    Registered User
    Join Date
    01-20-2016
    Location
    USA!
    MS-Off Ver
    Office 2010
    Posts
    62

    Developing Unique Dependent Dropdowns

    Hi all,

    Here's what I'm having trouble with:

    I have a spreadsheet with 5 columns, risk levels 1-5. On a separate tab, I want to have a dropdown for each of the 5, but have it list all corresponding values without the duplicates.

    For example, in the attached file you will see....Level 1 Risk, I only want Lawfulness listed once, and the second one should have Corporate Governance (listed once), Legal (listed once), level 3 Communication & Reporting Listed once.

    Basically I need each dropdown to be uniquely listed depending on the value of the dropdown before it and not have duplicates.

    Is this even possible?? Thanks for any help you can provide!!!
    Attached Files Attached Files

  2. #2
    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
    43,893

    Re: Developing Unique Dependent Dropdowns

    I think that this array formula is what you want.

    On sheet 2, A2, copied across and down:

    =IFERROR(INDEX(Sheet1!A$2:A$205,MATCH(0,COUNTIF(Sheet1!A$2:A$205,"<"&Sheet1!A$2:A$205)-SUM(COUNTIF(Sheet1!A$2:A$205,"="&A$1:A1)),0)),"")

    Array Formulae are a little different from ordinary formulae in that they MUST be confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER.

    You will know the array is active when you see curly brackets { } - or "curly braces" for those of you in the USA, or "flower brackets" for those of you in India - appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

    Don't type the curly brackets yourself - it won't work...

    Oh and yes... I've also sorted them alphabetically for you...
    Attached Files Attached Files
    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

  3. #3
    Registered User
    Join Date
    01-20-2016
    Location
    USA!
    MS-Off Ver
    Office 2010
    Posts
    62

    Re: Developing Unique Dependent Dropdowns

    Wow, thank you Glenn!~ Now comes my second question, how do I incorporate this into the dependent dropdown lists? It looks so fancy...

  4. #4
    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
    43,893

    Re: Developing Unique Dependent Dropdowns

    Now I think that I understand what you want.

    Ten Named Ranges, two fancy formulae and 5 data validations later... take a look at this. It's all on the one sheet, so that you can see what's going on.

    Start completing the first, second, etc, selections in column H and observe what goes on in columns A to E.

    the formulae in columns A to E are array formulae.

    Array Formulae are a little different from ordinary formulae in that they MUST be confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER.

    You will know the array is active when you see curly brackets { } - or "curly braces" for those of you in the USA, or "flower brackets" for those of you in India - appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

    Don't type the curly brackets yourself - it won't work...
    Attached Files Attached Files

  5. #5
    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
    43,893

    Re: Developing Unique Dependent Dropdowns

    Or... a macro-containing version which enables you to to a one-click reset...
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    01-20-2016
    Location
    USA!
    MS-Off Ver
    Office 2010
    Posts
    62

    Re: Developing Unique Dependent Dropdowns

    Glenn, you are simply a genius!!~ Thank you so much ))))

  7. #7
    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
    43,893

    Re: Developing Unique Dependent Dropdowns

    Let's hope that you're not being a bit premature with the accolades. Depending on what you want this for... there may yet be a snag with this.... We'll see.... You didn't provide ANY details of what you want to do with it.

    Caveat emptor...

+ 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. [SOLVED] Dependent Dropdowns with Values
    By scottaz in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 09-27-2015, 06:33 PM
  2. Dependent Dropdowns
    By ea223 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 02-26-2015, 12:33 PM
  3. [SOLVED] Dependent Dropdowns
    By Meabh in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-29-2015, 08:11 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. Replies: 2
    Last Post: 07-11-2013, 10:04 AM
  7. Dependent dropdowns
    By Jogier505 in forum Excel General
    Replies: 5
    Last Post: 12-30-2009, 08:39 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