Is there a way to have an excel cell contain multiple values in the filter function?-CodePudding

enter image description here

Here is a mockup of the table I am trying to build. Is it possible for the right most column to have its cells contain multiple values ? As in, I could be able to select a student name in the filter to make pop up all the class this specific student has missed.

enter image description here

I tried to use the "contain" filter but i have to write again the student name every time as the modalities of the columns become the list of absent student for each classes.

thanks in advance.

CodePudding user response：

You can make this work with a little VBA;

Setup the sheet like below with a cell (in green) in which to select a student name from a drop down (data validation) and a button to click to implement the filtering

Then name the button as GoButn and add the code below to the Sheet

Private Sub GoButn_Click()
   Dim SrchFor As String
   
   ActiveSheet.AutoFilter.ShowAllData
   SrchFor = "*" & Trim(Range("G2").Text) & "*"
   ActiveSheet.Range("MissingPPL").AutoFilter Field:=5, Criteria1:=SrchFor, Operator:=xlAnd
End Sub

In the above code the Green cell is "G2" and the Table has been named "MissingPPL"

Example below of selecting Jill and clicking Go

Hope this helps