Crazy Complicated - Advanced Filtering VBA based on cell value

Dear all,

I am working on a global email communications report covering 12 markets.
As per the screenshot attached, I would like to filter results based on the cell value in D3 ("Austria") whereas D3 is a drop-down listing all 12 markets in two variants (one is just the market e.g. "Austria" and one is "Austria_Trigger"). Based on what is selected in this drop down, a filter should be applied to all columns from row 14 to 8000 taking the campaign name as a reference (column D). Adding another layer of complexity: the sums of the results should only reflect the data of the visible rows once the filter is applied (if I filter manually, the sums include all hidden rows too, which is a problem).

An example:
If "Austria" is selected in D3, all results should filter and only show campaigns that contain "Austria" in the campaign name (however, EXCLUDING campaigns that contain "Austria_Trigger").
If "Austria_Trigger" is selected in D3, all results should filter and only show all campaigns that contain "Austria_Trigger" in the campaign name (EXCLUDING campaigns that contain "Austria" without the _Trigger extension).
The row displaying the sums for all results should change accordingly and only take data from the visible rows (not the filtered out ones).

Untitled-1.jpg

Hope this makes sense...

Would this be possible with VBA? How would I need to assign the macro to the drop-down in order to work?

Hope someone can help...would be highly appreciated!

Thanks,
Mat

Hi Mat,

To quickly answer your question about filtering:
-only "Austria" while excluding "Austria_Trigger", you can use ="=Austria" as a Filter
-only "Austria_Trigger" while excluding "Austria", you can use ="=Austria_Trigger*" as a Filter

Regards,
Rick

Hi Rick,

This is great - thanks for your help. I'll try to use subtotal then.
Can you let me know why you've included the 9 in this?:

=subtotal(9;F14:F8000)

Is there also a similar formula for average (e.g. "=subaverage")?

Regarding your answer concerning filters:

-only "Austria" while excluding "Austria_Trigger", you can use ="=Austria" as a Filter
-only "Austria_Trigger" while excluding "Austria", you can use ="=Austria_Trigger*" as a Filter

I'm not quite sure if I understand. That would be a manual filter, right?
I would like to automate this so depending on what markets select from the drop down, only the relevant campaigns would show up.

I've attached the report with example data (had to zip it as too big to upload here)
Performance_Report_2015.zip
...maybe this is of help to understand what I am trying to achieve?

Thanks for your help and time - really appreciate it!

Cheers,
Mat

Hi John,

Perfect! Thanks so much - that did the trick

Sorry for asking, but are you able to help re my other inquiry? Still can't this automatic filter to work :-( It's a nightmare...

Many thanks and happy Friday,
Mat

Hi John, Hi Rick,

I'm playing with the following code at the moment, but it does not seem to work properly...
Do you have an idea what I'm doing wrong? I'm on Excel 2010 if that makes any difference...

Sub Filter_ON()
Dim criteriaparameter As String
Dim criteriavalue As String

criteriaparameter = ActiveSheet.Range("D3").Value
criteriavalue = "=" & criteriaparameter

        ActiveSheet.Range("$D$14:$D$8013").AutoFilter Field:=4, Criteria1:=criteriavalue, _
        Operator:=xlAnd
        
End Sub
Sub Filter_OFF()
ActiveSheet.Range("$D$14:$D$8013").AutoFilter Field:=4
End Sub

Thanks,
Mat

Hi Rory,

OMG! You just saved my life! AMAZING! Works perfectly!!!
Do you think it would be possible that if I select "INTERNATIONAL" from the dropdown, that all results are shown (i.e. no filter applied?).
That would perfect...

Thanks so much!
Happy Friday,
Mat

Pefect! Thanks

I get a compile error "Invalid qualifier" highlighting the UCase$ bit... (I've added a close bracket as I felt it was missing...hope this didn't ruin it).

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim lLastRow                    As Long

    On Error GoTo catch

    If Not Intersect(Target, Range("D3")) Is Nothing Then
        Application.EnableEvents = False
        lLastRow = Cells(Rows.Count, "B").End(xlUp).Row
        If UCase$(Range("D3")).Value = "ALL_INTERNATIONAL" Then
            Range("D13:D" & lLastRow).AutoFilter
        ElseIf Right$(LCase$(Range("D3").Value), 8) = "_trigger" Then
            Range("D13:D" & lLastRow).AutoFilter field:=1, Criteria1:=Range("D3").Value & "*"
        Else
            Range("D13:D" & lLastRow).AutoFilter field:=1, Criteria1:=Range("D3").Value & "*", Operator:=xlAnd, Criteria2:="<>" & Range("D3").Value & "_Trigger*"
        End If
    End If

catch:
    Application.EnableEvents = True

End Sub

Appreciate your help.
Let me know if I can return the favor somehow...

Cheers,
Mat

Yaaay! Works!! Thanks so much! Let me know if I can return the favor.

Cheers and have a great weekend,
Mat