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.

An attachment has been added

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:

Please Login or Register  to view this content.

which is entered using CTRL SHIFT ENTER.

Cell K2 has the following formula :

Formula:

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:

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

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.

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

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.

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