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.
Bookmarks