Help With Slicers & Formulas Based On Filtered Table

t0ny84

Board Regular
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.

t0ny84

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

t0ny84

Board Regular
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.

VBA Code:
``````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

Well-known Member
You can check the slicer.
VBA Code:
``````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

Board Regular
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.

VBA Code:
``````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``````

Replies
1
Views
57
Replies
0
Views
324
Replies
1
Views
103
Replies
1
Views
300
Replies
0
Views
21

1,127,328
Messages
5,624,036
Members
416,007
Latest member
csf

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back