# Need Newbie Help! Need to return value's selected on filter • ### 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