Controling a Pivot Dropdown
I have an excel workbook with 2 worksheets named Pivot & Summary.
The Pivot worksheet contains a large & cumbersome pivot table which contains dated information. One of the page dropdowns on the pivot table is a month selector. The summary worksheet picks off relevant data from the pivot table on the Pivot worksheet and displays in an easy to read format. As it is, to view a different month's data, I have to select the Pivot worksheet, change the month and then go back to the summary worksheet to view the change. My question is, can I control or change the Month drop down control from the Summary worksheet? |
Controling a Pivot Dropdown
One way...
On the Summary sheet, create a dropdown list with ALL the valid selections for the Month page field - probably (all) plus every month. In my example, I used data validation and put this dropdown list in cell A1. Right-click on the (Summary) sheet tab and select 'View code'. The Visual Basic Editor will open. Paste the following code in the big blank white window: Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, Range("A1")) Is Nothing Then Exit Sub If Len(Target.Value) = 0 Then Exit Sub Sheets("Pivot").PivotTables(1).PivotFields("Month" ).CurrentPage = _ Sheets("Summary").Range("A1").Value End Sub Change "A1" to the cell where you added the dropdown list. Save the workbook, then press Alt-Q to close the Visual Basic Editor and return to regular Excel. Now the page field in the pivot table on the Pivot sheet should change whenever you change the selection in the dropdown cell on the Summary sheet. Hope this helps, Hutch "Steve" wrote: I have an excel workbook with 2 worksheets named Pivot & Summary. The Pivot worksheet contains a large & cumbersome pivot table which contains dated information. One of the page dropdowns on the pivot table is a month selector. The summary worksheet picks off relevant data from the pivot table on the Pivot worksheet and displays in an easy to read format. As it is, to view a different month's data, I have to select the Pivot worksheet, change the month and then go back to the summary worksheet to view the change. My question is, can I control or change the Month drop down control from the Summary worksheet? |
Controling a Pivot Dropdown
Thanks Tom . . . . .Worked exactly as I requested!!
"Tom Hutchins" wrote: One way... On the Summary sheet, create a dropdown list with ALL the valid selections for the Month page field - probably (all) plus every month. In my example, I used data validation and put this dropdown list in cell A1. Right-click on the (Summary) sheet tab and select 'View code'. The Visual Basic Editor will open. Paste the following code in the big blank white window: Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, Range("A1")) Is Nothing Then Exit Sub If Len(Target.Value) = 0 Then Exit Sub Sheets("Pivot").PivotTables(1).PivotFields("Month" ).CurrentPage = _ Sheets("Summary").Range("A1").Value End Sub Change "A1" to the cell where you added the dropdown list. Save the workbook, then press Alt-Q to close the Visual Basic Editor and return to regular Excel. Now the page field in the pivot table on the Pivot sheet should change whenever you change the selection in the dropdown cell on the Summary sheet. Hope this helps, Hutch "Steve" wrote: I have an excel workbook with 2 worksheets named Pivot & Summary. The Pivot worksheet contains a large & cumbersome pivot table which contains dated information. One of the page dropdowns on the pivot table is a month selector. The summary worksheet picks off relevant data from the pivot table on the Pivot worksheet and displays in an easy to read format. As it is, to view a different month's data, I have to select the Pivot worksheet, change the month and then go back to the summary worksheet to view the change. My question is, can I control or change the Month drop down control from the Summary worksheet? |
All times are GMT +1. The time now is 12:19 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com