Help With Slicers & Formulas Based On Filtered Table

[t0ny84]

Hi,

I am wondering if anyone can assist with the below questions, the spreadsheet design would be a single sheet with a table and two slicers setup (one for Name and one for Team).

1) Can SLICERs and their selections be accessed\used in formulas?

2) Can I show the value of what has been selected in the slicer in a cell.
e.g. John selected on Slicer and in Cell A1 says John.

3) Is it possible (preferably) via a formula to have a cell value updated based on filtered tables results.
e.g. On sheet select John - Cell A2 would calculate totals in filtered results for John from column 3 of the table.

4) A formula to show the value of the first cell in the filtered data in another cell.

Thanks In Advance!
t0ny84

 

[mart37]

slicer slection

 

[t0ny84]

slicer slection

Hey mart37 thanks for the link, I have read over this and like the idea but decided easier to go with a macro as it means less chance of end users breaking something!
Now just have to figure out how to get the below macro to ONLY work if Column 1 (NAME) is filtered. If it isn't then do nothing.

Private Sub Worksheet_Calculate()
   With Worksheets("TIL Data").ListObjects("tblTILData").AutoFilter.Range
   
       Worksheets("TIL Data").Range("G2").Value2 = Range("B" & .Offset(1, 0).SpecialCells(xlCellTypeVisible)(1).Row).Value2
    End With
End Sub

 

[mart37]

You can check the slicer.

y = 0
x = ActiveWorkbook.SlicerCaches("Slicer_Name").SlicerItems.Count
For Z = 1 To x
    If ActiveWorkbook.SlicerCaches("Slicer_Name").SlicerItems(Z).Selected Then y = y + 1
Next
If y = Z - 1 Then
    MsgBox "not filtered"
Else
    MsgBox "filtered"
End If

 

[t0ny84]

Hey Mart,

Thanks for the code, I will add this to my code base for future usage. For this occasion a colleague helped me with the following code.
It checks if column 2 (NAME) and if sorted then changes the cell information.
If not filtered by this column then does nothing.

Private Sub Worksheet_Calculate()
    strFilter = ""
    On Error Resume Next
    strFilter = Application.Range("tblTILData").ListObject.AutoFilter.Filters.Item(Application.Range("TABLE1").ListObject.ListColumns("Name").Index).Criteria1
    On Error GoTo 0
    If Not strFilter = "" Then
 
 With Worksheets("SHEET1").ListObjects("TABLE1").AutoFilter.Range
 Worksheets("SHEET1").Range("G2").Value2 = Range("B" & .Offset(1, 0).SpecialCells(xlCellTypeVisible)(1).Row).Value2
    
    End With
    End If
    
End Sub