|
Subject:
|
Dynamic Pivot Table Filter??
|
|
Posted By:
|
tready
|
Post Date:
|
1/17/2006 1:57:27 PM
|
Is there anyway I can Filter one of the fields a pivot table shows based on the content of a cell?
Example:
I create a generic pivot table that shows all data but when the spreadsheet loads it looks at that cell and filters the data. Anyone know of how to make this work OR a better way to do this?
|
|
Reply By:
|
Wulffman
|
Reply Date:
|
2/21/2006 12:27:24 AM
|
I am getting close to this.
I have 2 cells with drop down lists.
I have a macro that I manually fire:
Sub UpdatePivots()
a = Worksheets("BuildQry").Cells(6, 1)
Sheets("BuildQry").PivotTables("PivotTable2").PivotFields("ITDept").CurrentPage = a
a = Worksheets("BuildQry").Cells(8, 1)
Sheets("BuildQry").PivotTables("PivotTable4").PivotFields("ITName").CurrentPage = a
End Sub
This causes the pivot table to be updated based on the contents of the two cells. I am struggling with getting the macro to fire on the DropButtonClick() Event:
Private Sub Worksheet_DropButtonClick()
a = Worksheets("BuildQry").Cells(6, 1)
Sheets("BuildQry").PivotTables("PivotTable2").PivotFields("ITDept").CurrentPage = a
a = Worksheets("BuildQry").Cells(8, 1)
Sheets("BuildQry").PivotTables("PivotTable4").PivotFields("ITName").CurrentPage = a
End Sub
I will post if I get thru my struggles. Take care. -t
|
