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)