Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pivot table active dropdown | Excel Worksheet Functions | |||
Pivot Chart Dropdown Question | Excel Programming | |||
pivot dropdown list limits | Excel Discussion (Misc queries) | |||
controling a .xls from another .xls | New Users to Excel | |||
pivot dropdown list | Excel Worksheet Functions |