none
Need Newbie Help! Need to return value's selected on filter RRS feed

  • Question

  • Hi All,

    Total newbie here... I'm trying to return the values of the filters..

    Example:

    Filter categories are : "Must Do", "Must Do Later", "Nice to Have"

    Depending on which filters are triggered, I want a cell to return the filtered categories while ignoring the blanks (blanks would always be selected).

    i.e. if "Must Do" and "Must Do Later" is selected, then return "Must Do and Must Do Later"

    or if if "Must Do" is selected, then return "Must Do"

    or if "Must Do" and "Must Do Later" and "Nice to Have" are selected, then return "Must Do, Must Do Later and Nice to Have"

    Thanks!!

    Tuesday, February 7, 2017 3:07 PM

All replies

  • The simplest thing to do is to use a formula like

    =IF(SUBTOTAL(3,A2)=1,"Must Do ","") & IF(SUBTOTAL(3,A3)=1,"Must Do Later ","") & IF(SUBTOTAL(3,A4)=1,"Nice to Have","")

    Where A is the column with categories, and A2 is a cell that has "Must Do", A3 has "Must Do Later" and A4 has "Nice to Have"

    You can get trickier to do the combinations instead but that requires a longer formula - it just doesn't add a lot:

    =IF(AND(SUBTOTAL(3,A2)=1, SUBTOTAL(3,A3)=1, SUBTOTAL(3,A4)=1),"Must Do, Must Do Later, and Nice to Have", IF(AND(SUBTOTAL(3,A2)=1, SUBTOTAL(3,A3)=1),"Must Do and Must Do Later",IF(AND(SUBTOTAL(3,A2)=1, SUBTOTAL(3,A4)=1),"Must Do and Nice to Have",IF(AND(SUBTOTAL(3,A3)=1, SUBTOTAL(3,A4)=1),"Must Do Later and Nice to Have",IF(SUBTOTAL(3,A2)=1,"Must Do", IF(SUBTOTAL(3,A3)=1, "Must Do Later",IF(SUBTOTAL(3,A4)=1,"Nice to Have","None Selected")))))))

    Your cells A2, A3, and A4 don't need to be contiguous....

    Tuesday, February 7, 2017 7:28 PM
  • Hi,

    Depending on the your describtion I think what you need is criteria of filters

    You can try this code.

    Sub Test()
    Dim count As Integer
    Dim boo As Boolean
    Dim arr As Variant
    Dim STR As String
    boo = ActiveSheet.AutoFilter.Filters(1).On
        If boo Then
            arr = ActiveSheet.AutoFilter.Filters(1).Criteria1
            count = ActiveSheet.AutoFilter.Filters(1).count - 1 
            Select Case count
            Case 1
            STR = arr
            Case 2
            STR = arr(1) & " and " & arr(2)
            Case 3
            STR = arr(1) & ", " & arr(2) & " AND " & arr(3)
            End Select
        Else
            STR = "Must Do, Must Do Later AND Nice to Have"
        End If
        STR = Replace(STR, "=", "")
    ActiveSheet.Cells(1, "A") = STR
    End Sub

    The code build on "blanks would always be selected".Please make sure it invariant

    Hope it can help you

    Thursday, February 9, 2017 9:05 AM