Data Validation Hide Previously Used Items in Dropdown Based on 2 Columns/Criteria

Hi:

I need help on how to hide previously used item in a dropdown list based on 2 columns/criteria.

I have input names namely George, Dylan, Linda, Leader in Column A (Names). In another sheet (Sheet 2) I have a list their in Column A (Sessions), Session 1 - Session 25 (for data validation purposes); I also have in Column B (Type), Ongoing, and Completed.

Basically, what will happen is that

1. Once I input George in Column A (Names) and I selected in the dropdown list (lets say in Column B of the same Sheet) Session 1, we won't be seeing anymore the Session 1 option.
2. After which, if I add another name Dylan in Column A, I will see Session 1 again along with Session 2 to Session 25 and so on in the drop down list.
3. If I selected Completed in Column C with the same Name (George) and it so happens it ended only in Session 3 (selected in drop down list in Column B). I will have a new set of Session 1 to Session 25 in the drop down list once I input George's name again in Column A.
   

I checked the website,

HTML Code: 

and I get how it is done on hiding the previously used items in drop down list. However, what if it is based on a column with different names? and another column? How can it be done?


Kindly see attached file for a sample output for your reference. Thanks in advance.

=OFFSET(x!$H$2,,MATCH(l!$A2,x!$H$1:$K$1,)-1,COUNTIF(OFFSET(x!$H$2,,MATCH(l!$A2,x!$H$1:$K$1,)-1,300,),">"""),)
the rest are in the file