Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
pivot table comparison
Ok new question! I've been trying to work this out, but i havent been
successful. I want when i select the Quarters to be displayed on one pivot table, that those quarters be the same ones displayed on all the pivot tables. I know i have to use PivotTable("name"), PivotField("Quarters") and then use PivotItems.visible = true. I have to have one for my source data and then one that will be changing to do the comparison, but I'm not exactly sure on how to loop through the pivotitems and compare that to my main pivotitems. If you need a better explanation please let me know, these pivot table issues are pretty complex. Thanks, Tim |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
pivot table comparison
There's a sample file here that changes multiple pivot tables, based on
selections in the page field of the main pivot table: http://www.contextures.com/excelfiles.html Under Pivot Tables, look for 'Change Page Field' Tim wrote: Ok new question! I've been trying to work this out, but i havent been successful. I want when i select the Quarters to be displayed on one pivot table, that those quarters be the same ones displayed on all the pivot tables. I know i have to use PivotTable("name"), PivotField("Quarters") and then use PivotItems.visible = true. I have to have one for my source data and then one that will be changing to do the comparison, but I'm not exactly sure on how to loop through the pivotitems and compare that to my main pivotitems. If you need a better explanation please let me know, these pivot table issues are pretty complex. Thanks, Tim -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
pivot table comparison
Debra,
I've implemented that already into the solution. I'm going to use that page field example to illustrate what i'm wanting to do. Under sales Pivot i want to select the Employee record, say Gil, Howard and Jones and that be the only records being shown on the other pivot tables as well. Since the Item is a drop down box, only one item can be selected. This is now a list but i cant seem to figure out how to just say currentlist1 = maincurrentlist. "Debra Dalgleish" wrote: There's a sample file here that changes multiple pivot tables, based on selections in the page field of the main pivot table: http://www.contextures.com/excelfiles.html Under Pivot Tables, look for 'Change Page Field' Tim wrote: Ok new question! I've been trying to work this out, but i havent been successful. I want when i select the Quarters to be displayed on one pivot table, that those quarters be the same ones displayed on all the pivot tables. I know i have to use PivotTable("name"), PivotField("Quarters") and then use PivotItems.visible = true. I have to have one for my source data and then one that will be changing to do the comparison, but I'm not exactly sure on how to loop through the pivotitems and compare that to my main pivotitems. If you need a better explanation please let me know, these pivot table issues are pretty complex. Thanks, Tim -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
pivot table comparison
You could move the page field to the row area in the code, hide matching
items in the other pivot tables, then move the field back to the page area. For example: '============================ Sub PivotHidePageItems() Dim wsSP As Worksheet Dim wsOP As Worksheet Dim ptMain As PivotTable Dim pfMain As PivotField Dim pt As PivotTable Dim pf As PivotField Dim pi As PivotItem Dim strField As String Set wsSP = Sheets("Sales Pivot") Set wsOP = Sheets("Other Pivots") Set ptMain = wsSP.PivotTables(1) strField = "Item" Set pfMain = ptMain.PivotFields(strField) Application.ScreenUpdating = False Application.DisplayAlerts = False Application.EnableEvents = False With pfMain .Orientation = xlRowField .Position = 1 End With For Each pt In wsOP.PivotTables Set pf = pt.PivotFields(strField) On Error Resume Next With pf .CurrentPage = "(All)" .AutoSort xlManual, .SourceName 'show all pivot items For Each pi In pf.PivotItems pi.Visible = True Next pi 'hide pivot items to match main table For Each pi In pf.PivotItems If pfMain.PivotItems(pi.Name).Visible = False Then pi.Visible = False End If Next pi .AutoSort xlAscending, .SourceName End With Next pt With pfMain .Orientation = xlPageField .Position = 1 End With Application.DisplayAlerts = True Application.ScreenUpdating = True Application.EnableEvents = True End Sub '=================================== Tim wrote: Debra, I've implemented that already into the solution. I'm going to use that page field example to illustrate what i'm wanting to do. Under sales Pivot i want to select the Employee record, say Gil, Howard and Jones and that be the only records being shown on the other pivot tables as well. Since the Item is a drop down box, only one item can be selected. This is now a list but i cant seem to figure out how to just say currentlist1 = maincurrentlist. "Debra Dalgleish" wrote: There's a sample file here that changes multiple pivot tables, based on selections in the page field of the main pivot table: http://www.contextures.com/excelfiles.html Under Pivot Tables, look for 'Change Page Field' Tim wrote: Ok new question! I've been trying to work this out, but i havent been successful. I want when i select the Quarters to be displayed on one pivot table, that those quarters be the same ones displayed on all the pivot tables. I know i have to use PivotTable("name"), PivotField("Quarters") and then use PivotItems.visible = true. I have to have one for my source data and then one that will be changing to do the comparison, but I'm not exactly sure on how to loop through the pivotitems and compare that to my main pivotitems. If you need a better explanation please let me know, these pivot table issues are pretty complex. Thanks, Tim -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pivot Table Comparison | Excel Worksheet Functions | |||
pivot table comparison | Excel Discussion (Misc queries) | |||
Pivot comparison | Excel Discussion (Misc queries) | |||
Pivot Table row comparison | Excel Discussion (Misc queries) | |||
how do I set up a fast comparison table? | Excel Discussion (Misc queries) |