Help With Slicers & Formulas Based On Filtered Table

t0ny84

Board Regular
Joined
Jul 6, 2020
Messages
134
Office Version
  1. 365
  2. 2016
  3. 2013
Platform
  1. Windows
  2. Mobile
  3. Web
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
 

Some videos you may like

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

t0ny84

Board Regular
Joined
Jul 6, 2020
Messages
134
Office Version
  1. 365
  2. 2016
  3. 2013
Platform
  1. Windows
  2. Mobile
  3. Web
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
Joined
Aug 4, 2017
Messages
1,044
Office Version
  1. 2016
Platform
  1. Windows
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
 
Solution

t0ny84

Board Regular
Joined
Jul 6, 2020
Messages
134
Office Version
  1. 365
  2. 2016
  3. 2013
Platform
  1. Windows
  2. Mobile
  3. Web
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
 

Watch MrExcel Video

Forum statistics

Threads
1,127,001
Messages
5,622,126
Members
415,878
Latest member
jjj12345

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top