Hi,
is it possible to Change the value in a Pivot table filter via Combo box or data Validation list?
Hi Andrew,
In session 5.08 of the Dashboard course I cover how to do this with a macro connected to a combo box. Let me know if you get stuck.
Mynda
Hi Mynda,
Thanks for answering so quickly. I tried the method in session 5.08 and everything worked perfectly where the source data and Pivot tables are all in the same workbook.
I am having Trouble getting My head around the VBA that omes up when I try changing Pivot tables that are fed by data from a data model via power query.
Here's what the VBA Looks like after I record the macro as in 5.08
'
' ChangePivFilter Makro
'
ActiveSheet.PivotTables("PivotTable1").PivotFields( _
"[Lieferschein_Raw_Data].[Auswertekunde Name].[Auswertekunde Name]"). _
ClearAllFilters
ActiveSheet.PivotTables("PivotTable1").PivotFields( _
"[Lieferschein_Raw_Data].[Auswertekunde Name].[Auswertekunde Name]"). _
CurrentPage = "[Lieferschein_Raw_Data].[Auswertekunde Name].&[Alwin Berghoff]"
ActiveSheet.PivotTables("PivotTable2").PivotFields( _
"[Lieferschein_Raw_Data].[Auswertekunde Name].[Auswertekunde Name]"). _
ClearAllFilters
ActiveSheet.PivotTables("PivotTable2").PivotFields( _
"[Lieferschein_Raw_Data].[Auswertekunde Name].[Auswertekunde Name]"). _
CurrentPage = "[Lieferschein_Raw_Data].[Auswertekunde Name].&[Alwin Berghoff]"
End Sub
Hi Andrew,
Why don't you use Slicers instead? I only recommend the VBA approach for users on Excel 2007 who don't have the luxury of Slicers. They are designed to do what you want without the need for VBA. Session 6.02 of the Dashboard course covers Slicers.
Mynda
Hi Mynda,
unfortunately slicers are not an option, the Pivot tables don't share the same source data and I can't find a way to get them on common Ground without using helper tables and loads of Vlookups, I've even tried to connect them in Power Pivot with help tables but to no effect.
I thought the combo box choice would be an easier alternative.
Andrew
Hi Andrew,
A combo might be an easier alternative, but not the right way to go.
To make your macro work, you have to extract the name based on that combo index value from the same list that is provided to combo, then adjust the macro to use the dynamic name instead of that recorded static name:
Dim IndexValue As Long, Nm As String
'get the index value
IndexValue = ThisWorkbook.Worksheets("Unique_Auswert_Liste").Range("D7")
'based on index value, get the name from the list that fills the combo:
'(change the range to your real range location)
Nm = Application.WorksheetFunction.Index(ThisWorkbook.Worksheets("Unique_Auswert_Liste").Range("H1:H10"), IndexValue)
'use the new name in pivot filters:
ActiveSheet.PivotTables("PivotTable1").PivotFields( _
"[Lieferschein_Raw_Data].[Auswertekunde Name].[Auswertekunde Name]"). _
CurrentPage = "[Lieferschein_Raw_Data].[Auswertekunde Name].&[" & Nm & "]"
If you need more help to make it work, please provide a sample file with your data structure.
