+ Reply to Thread
Results 1 to 9 of 9

Dynamic Drop Down List using criteria from another dynamic drop down list excluding blanks

  1. #1
    Registered User
    Join Date
    08-29-2018
    Location
    Melbourne, Australia
    MS-Off Ver
    2016/365 both
    Posts
    15

    Dynamic Drop Down List using criteria from another dynamic drop down list excluding blanks

    Good Morning
    I have 800 student names in rows and 24 subjects in columns, transformed via Power Query from a sql database. My first drop down list of student names [StudentNameNoYearLevel] autocompletes using the combo-box and code. Working well. I want the second validation dropdown list to let the user select the student's relevant subject [ClassCode] based on the first dropdown list selection. Some students have 6 subjects whilst others might have max 24 subjects; hence some columns are not filled. Given new students are added to the query during the year, I can't use the range name method; I need it to dynamically update. I intend on using the data validation list with a formula which looks like this so far:

    =OFFSET(Subjects[[#Headers],[ClassCodeFullColumns.1]],MATCH($B2,Subjects[StudentNameNoYearLevel],0),0,1,1)

    where $B2 is the result from the first drop-down list. I don't know how to complete the formula with the above giving me the first subject (8SCI2A) correctly against the right student, due to the offset commencing on the first subject header. However I need all 24 columns in the drop down list should that one selected student have 24 subjects.

    Any help is appreciated.... Thank you.

  2. #2
    Registered User
    Join Date
    08-29-2018
    Location
    Melbourne, Australia
    MS-Off Ver
    2016/365 both
    Posts
    15

    Re: Dynamic Drop Down List using criteria from another dynamic drop down list excluding bl

    An attachment has been added
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    12-03-2012
    Location
    CHENNAI , INDIA
    MS-Off Ver
    Excel 2007
    Posts
    93

    Re: Dynamic Drop Down List using criteria from another dynamic drop down list excluding bl

    Hi ,

    See if this works.

    Two helper columns have been used , columns J and K.

    Cells J2 through J25 (24 cells since you have said the number of courses is 24) have a multi-cell array formula :

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    which is entered using CTRL SHIFT ENTER.

    Cell K2 has the following formula :

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    This is a single-cell array formula , entered using CTRL SHIFT ENTER. Copy this formula down till K25 (24 cells).

    Now , a named range Courses_DV has been defined , with the following formula :

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    This basically excludes the blank cells from the subjects list in column K.

    Use this named range for the Data Validation list in cell D18.

    See the attached file.

    Narayan
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    08-29-2018
    Location
    Melbourne, Australia
    MS-Off Ver
    2016/365 both
    Posts
    15

    Re: Dynamic Drop Down List using criteria from another dynamic drop down list excluding bl

    Thank you Narayan, as it works for the one cell. However this is a growing list which other staff are entering data into (there will be more columns in the look up section of the worksheet, which will be the next part of the build. Hence in C19 another student will be added and it would be great if the the second subject drop down list is available for the new student in the list. This spreadsheet is extracting details for data accuracy from Current Student and Timetable Tables as the software database doesn't have the right fields for "Students with Disabilities".

    I could also consider changing the power query to provide the data in a better format if that would make this dynamic drop down easier to work with.....

    I appreciate you looking at this.

  5. #5
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Sadly... in lockdown, but based in Ireland.
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    28,653

    Re: Dynamic Drop Down List using criteria from another dynamic drop down list excluding bl

    Creaye a Named Range (Secondary - CTRL-F3 to view/Edit):
    =INDEX(Example!$C$2:$G$15,MATCH(Example!C18,Student,0),1):INDEX(Example!$C$2:$G$15,MATCH(Example!C18,Student,0),SUMPRODUCT(--(LEN(INDEX(Example!$C$2:$G$15,MATCH(Example!C18,Student,0),))>0)))

    Then use List = Secondary inthe yellow cell.
    Attached Files Attached Files
    Glenn



  6. #6
    Registered User
    Join Date
    12-03-2012
    Location
    CHENNAI , INDIA
    MS-Off Ver
    Excel 2007
    Posts
    93

    Re: Dynamic Drop Down List using criteria from another dynamic drop down list excluding bl

    Hi ,

    We could have a named range called SelectedStudent , which will change with the cell where the cursor is placed in column C.

    When the cursor is placed in cell C18 , the name of the selected student will be retrieved from C18.

    When the cursor is placed in cell C19 , the name of the selected student will be retrieved from C19.

    And so on.

    Each time the cursor is moved , the cells in columns J and K will be calculated afresh , and the Data Validation list Courses_DV will contain new items.

    See the attached file.

    Narayan
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    08-29-2018
    Location
    Melbourne, Australia
    MS-Off Ver
    2016/365 both
    Posts
    15

    Re: Dynamic Drop Down List using criteria from another dynamic drop down list excluding bl

    Thanks Glen, that's great. Only thing is that it seems to stop counting if a blank cell is found i.e. the subject drop down list for [Chat, Janet] doesn't include the fourth column (12BUS1B). Although with Marianne Noel it shows three to select from but not the fourth. (the second one being the blank in the drop down list. I haven't evaluated the formulas as yet to see why....

    Thanks Again.

  8. #8
    Registered User
    Join Date
    08-29-2018
    Location
    Melbourne, Australia
    MS-Off Ver
    2016/365 both
    Posts
    15

    Re: Dynamic Drop Down List using criteria from another dynamic drop down list excluding bl

    Perfect Narayan: I really appreciate your perseverance. I have learnt something new (the LEN function) and haven't seen this method after researching this problem for hours!!!!

    Best regards...Stefj

  9. #9
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Sadly... in lockdown, but based in Ireland.
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    28,653

    Re: Dynamic Drop Down List using criteria from another dynamic drop down list excluding bl

    Yes, you're right. I hadn't noticed the gaps in the middle of the data table

+ 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] Remove blanks from drop down list within dynamic table
    By jaryszek in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 08-20-2018, 11:14 AM
  2. Replies: 3
    Last Post: 06-29-2017, 04:19 PM
  3. Dynamic drop down list, based on a criteria
    By marcinpec in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 07-17-2014, 11:00 AM
  4. [SOLVED] Show data with month criteria drop list (dynamic)
    By Jhon Mustofa in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 03-16-2014, 12:54 PM
  5. [SOLVED] Dynamic Drop Down List - exclude blanks
    By Econocrat in forum Excel General
    Replies: 7
    Last Post: 08-24-2012, 02:10 PM
  6. create a dynamic drop down list based on specific criteria
    By stevegrobertson in forum Excel General
    Replies: 4
    Last Post: 02-21-2012, 08:07 PM
  7. Dynamic Drop Down List...with Criteria
    By TimE in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 07-17-2008, 11:43 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1