Extract a List of Values Filtered by Criteria

Hello everyone,

I'm working on a way to filter a running table I have using criteria that is dynamic. I had originally hoped to use VBA for this, but to no avail.

I attached an example of what I am hoping to accomplish; on Sheet1, whatever combination of cells G2 and H2 are, I would like the filtered results from Sheet2 to be compiled within columns A:D on Sheet1. I know I could just use AutoFilter on the actual table within Sheet2, but the end goal is to have this as a front-end worksheet for the user, so minimum interaction with the database is preferred.

Hope I was clear, and thanks to everyone in advance for any help.

Put this in the worksheet module for Sheet1:

Private Sub Worksheet_Change(ByVal Target As Range)
    On Error GoTo Terminate
    If Not Intersect(Target, Range("G2:H2")) Is Nothing Then
        With Application
            .ScreenUpdating = False
            .EnableEvents = False
        End With
        Range("A1").CurrentRegion.Offset(1, 0).ClearContents
        With Sheet2
            .Range("Table5").AutoFilter _
                Field:=Application.Match(Target.Offset(-1, 0).Value, .Range("Table5[#Headers]"), 0), _
                Criteria1:=YesNo(Target.Value)
            .AutoFilter.Range.SpecialCells(xlCellTypeVisible).Copy
            Sheet1.Range("A1").PasteSpecial (xlPasteValues)
            Application.CutCopyMode = False
        End With
        Target.Activate
    End If
Terminate:
    If Err.Number Then
        Debug.Print Err.Number, Err.Description
        Err.Clear
    End If
    With Application
        .ScreenUpdating = True
        .EnableEvents = True
    End With
End Sub

Function YesNo(s As String) As String
    YesNo = UCase(CStr(UCase(s) = "YES"))
End Function

FlameRetired,

I appreciate the catch! I updated the original example to reflect what I actually meant.

Olly,

I appreciate the coding help. I entered the code into the worksheet module for Sheet 1, but unfortunately it doesn't seem to be working on my end. There are no results on Sheet1 when G2 and H2 change, and Sheet2 seems to clear it's filter to show no results.

Thank you both for the feedback.

My code translates YES / NO into TRUE / FALSE, to work with the workbook you uploaded... so if you've already changed those values somewhere, that will cause a problem.

It's written to debug error descriptions to the immediates window - is there an error being returned after you change G2 or H2?

In case it helps, here's your original file with my code added, and tested working on my pc:mcmahobt.xlsm

Olly, I appreciate the quick and thorough help! I really need to just bite the bullet and get into some books on VBA...

FlameRetired, thanks for the effort. Although close to what I am looking for, my original hope was to pull something together within VBA and Olly's method should hopefully do the trick.