Hey Guys,
In my efforts to find a way to use drop down lists to filter a pivot table report, I found the following in an archive that indicated that it would solve the problem:
Question
Now that I'm using the function GETPIVOTDATA, creating reports the way I want them to look is much easier. I've got the pivot table itself on one spreadsheet&the report with the getpivotdata function on another sheet. How do make the report dynamic without having to go to the pivot table to change a page (filter) item? In otherwords, I want the end-user to open the report (with the pivot table sheet hidden) & have the ability to choose which product (the filter item) numbers to look at. Many thanks, John
Answer
You could create a dropdown list of pages, by using data validation, then use a worksheet change event to update the table created with GETPIVOTDATA.
1. In an empty column in the workbook, type a list of items in the Page
dropdown list (e.g. Customer names)
2. Select the cells in the list, and click in the Name Box (left of the
formula bar).
3. Type a one-word name for the range, e.g. CustomerList, then press the
Enter key.
4. Select a cell near your GetPivotData table, where you would like a
drop-down list to appear. (cell D1 in this example)
5. Choose Data>Validation
6. In the Allow box, choose List
7. In the Source box, type an equal sign and the list name, e.g.
=CustomerList
8. Click OK
9. Right-click on the sheet tab, and choose View Code.
10. Paste in the following code, changing the cell references to match your
workbook.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim strCust As String
If Target.Address = "$D$1" Then
strCust = Target.Value
Worksheets("Sheet4").PivotTables("PivotTable1") _
.PivotFields("Customer").CurrentPage = strCust
End If
End Sub
So, I created a small, simple spreadsheet (which I have uploaded) to test the above solution. When I select an item from my drop down list, I get a runtime error message that says "Unable to get the Pivot Tables property of the worksheet class."
Any help with why this isn't working would be appreciated. Thanks.
In my efforts to find a way to use drop down lists to filter a pivot table report, I found the following in an archive that indicated that it would solve the problem:
Question
Now that I'm using the function GETPIVOTDATA, creating reports the way I want them to look is much easier. I've got the pivot table itself on one spreadsheet&the report with the getpivotdata function on another sheet. How do make the report dynamic without having to go to the pivot table to change a page (filter) item? In otherwords, I want the end-user to open the report (with the pivot table sheet hidden) & have the ability to choose which product (the filter item) numbers to look at. Many thanks, John
Answer
You could create a dropdown list of pages, by using data validation, then use a worksheet change event to update the table created with GETPIVOTDATA.
1. In an empty column in the workbook, type a list of items in the Page
dropdown list (e.g. Customer names)
2. Select the cells in the list, and click in the Name Box (left of the
formula bar).
3. Type a one-word name for the range, e.g. CustomerList, then press the
Enter key.
4. Select a cell near your GetPivotData table, where you would like a
drop-down list to appear. (cell D1 in this example)
5. Choose Data>Validation
6. In the Allow box, choose List
7. In the Source box, type an equal sign and the list name, e.g.
=CustomerList
8. Click OK
9. Right-click on the sheet tab, and choose View Code.
10. Paste in the following code, changing the cell references to match your
workbook.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim strCust As String
If Target.Address = "$D$1" Then
strCust = Target.Value
Worksheets("Sheet4").PivotTables("PivotTable1") _
.PivotFields("Customer").CurrentPage = strCust
End If
End Sub
So, I created a small, simple spreadsheet (which I have uploaded) to test the above solution. When I select an item from my drop down list, I get a runtime error message that says "Unable to get the Pivot Tables property of the worksheet class."
Any help with why this isn't working would be appreciated. Thanks.