+ Reply to Thread
Results 1 to 6 of 6

Dependent Drop down list

  1. #1
    Registered User
    Join Date
    03-12-2014
    Location
    Singapore
    MS-Off Ver
    Office 365 online
    Posts
    16

    Dependent Drop down list

    Dear All Excel Guru (need help Obi Wan formuola),

    I am trying to complete a dependent drop down list - which one excel forum member was very kind to assist with, however i am trying to figure out the following- and not having much luck - running out of time to get this S/S done as well.

    The dependent criteria will be fixed string EG "Retired"
    The list of data to search upon is not sorted... (see pic)

    That being said - I actually want to create a list in the drop down of all course which are not equal "retired"

    this is the formula to -date based on eq to "retired" (B2 being retired) , be seems to return the first retired course and all the currents in between as well


    =OFFSET('Static Data'!$J$1,MATCH(B2,'Static Data'!M:M,0)-1,,LOOKUP(2,1/('Static Data'!$M$5:$M$40=B2),ROW('Static Data'!$M$5:$M$40))-MATCH(B2,'Static Data'!M:M,0)+1)
    Attached Images Attached Images
    Attached Files Attached Files
    Last edited by Itolond; 07-07-2014 at 04:48 AM. Reason: upload file

  2. #2
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,635

    Re: Dependent Drop down list

    your file does not match your screenshot or your post?
    If you are satisfied with the solution(s) provided, please mark your thread as Solved.
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

  3. #3
    Registered User
    Join Date
    03-12-2014
    Location
    Singapore
    MS-Off Ver
    Office 365 online
    Posts
    16

    Re: Dependent Drop down list

    oops -updated with correct file

  4. #4
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,635

    Re: Dependent Drop down list

    as i am unsure how to do it in one formula

    i used combination of offset formula and a helper column

    helper column (CSE formula)
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    not sure if that countif is required for this particular example as you should not have duplicates? but just in case

    Offset formula (for named range)
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    03-12-2014
    Location
    Singapore
    MS-Off Ver
    Office 365 online
    Posts
    16

    Re: Dependent Drop down list

    Great stuff will take a look. I wasnt sure what the countif function provided in terms of listing, But now i understand. THX

  6. #6
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,635

    Re: Dependent Drop down list

    the countif in the helper eliminates duplicates should that be a problem for you

    the countif in the offset works in combination with counta with counts the whole array length

    in the example
    COUNTA returns 36
    countif returns 33
    so your offset array length is 3

    if you dont include your DV will be very long with a lot of empty spaces
    Last edited by humdingaling; 07-08-2014 at 03:43 AM.

+ 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 Drop down list with DEPENDENT DEFAULT VALUE
    By ginieman in forum Excel Programming / VBA / Macros
    Replies: 17
    Last Post: 10-28-2013, 03:56 AM
  2. [SOLVED] Suppress drop-down list dependent on value chosen in another drop-down list
    By ptmald in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-17-2013, 07:17 PM
  3. [SOLVED] Column B drop down list with 2 options, columns C & E with dependent drop down lists
    By betic in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 09-26-2012, 03:47 PM
  4. [SOLVED] 2nd Drop Down List Dependent on 1st Drop Down List Creation
    By LWABowler in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 08-21-2012, 03:33 AM
  5. [SOLVED] Dependent Drop down list with DEPENDENT DEFAULT VALUE (e.g. Blank)
    By Waqar Ali in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 04-26-2012, 06:31 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