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?
|
Similar Threads
