Select multiple slicer value based on cell values

sdas34

New Member
Joined
Jan 8, 2021
Messages
8
Office Version
  1. 365
Platform
  1. Windows
I have 2 different data source but 1 common field. I have inserted 2 individual slicers for those 2 data sets. Now I want to create a vba code where I can manually enter a value in a particular cell and on that cell value both the slicers gonna filter. for example: I have common field name"Year" and "Quarter". these 2 fields are common in 2 different data set. Now I have inserted 2 slicers for Year and 2 slicers for Quarter. So I'd like to manually type in a cell 2020 and in another cell Q3 and all 4 slicers should get updated. Also if no value is matched for a particular data set then it should show all the value for that data I tried the following code but it's helpful only for 1 slicer at a time:

Sub Worksheet_Change(ByVal Target As Range)
Dim sc As SlicerCache, si As SlicerItem
If Target.Address = "$K$1" Then
Set sc = ActiveWorkbook.SlicerCaches("Fiscal Year") ' desired slicer
sc.ClearAllFilters
For Each si In sc.SlicerItems
If si.Caption = CStr(Target) Then
si.Selected = True
Else
si.Selected = False
End If
Next
End If
End Sub
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

Forum statistics

Threads
1,224,847
Messages
6,181,325
Members
453,032
Latest member
Pauh

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
Back
Top