Home |
Search |
Today's Posts |
#1
|
|||
|
|||
pivot table created from another pivot table
I was able to create a pivot table using an established pivot table; however,
the established pivot table has filters (created using the PAGE area of the layout). I was hoping that when the established pivot table is filtered on (thus changing the data within the pivot), it would update the newly created pivot with the same filters. Is there something I should do that would acheive what I desire in this second pivot table? Thanks! |
#2
|
|||
|
|||
You can do this with programming. There's a sample file he
http://www.contextures.com/excelfiles.html Under PivotTables, look for 'Change Page Field' Kreed wrote: I was able to create a pivot table using an established pivot table; however, the established pivot table has filters (created using the PAGE area of the layout). I was hoping that when the established pivot table is filtered on (thus changing the data within the pivot), it would update the newly created pivot with the same filters. Is there something I should do that would acheive what I desire in this second pivot table? Thanks! -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
#3
|
|||
|
|||
pivot table created from another pivot table
This is very much on the track that I need to be on. From what I see in the
sample file, it is in visual basic that I would need to do this programming. I can somewhat follow what programming; however, I have a few attributes in the Page field that I would need to link. What could I do with this programming to help me get there? Here is the programming that I copied: Option Explicit Dim mvPivotPageValue As Variant Private Sub Worksheet_Calculate() Dim wsOther As Worksheet Dim pt As PivotTable Dim pt1 As PivotTable Dim pt2 As PivotTable Dim strField As String Set wsOther = Sheets("Other Pivots") Set pt = Me.PivotTables(1) Set pt1 = wsOther.PivotTables(1) Set pt2 = wsOther.PivotTables(2) strField = "Market" If LCase(pt.PivotFields("Market").CurrentPage) < LCase(mvPivotPageValue) Then Application.EnableEvents = False pt.RefreshTable mvPivotPageValue = pt.PivotFields(strField).CurrentPage pt1.PageFields(strField).CurrentPage = mvPivotPageValue pt2.PageFields(strField).CurrentPage = mvPivotPageValue Application.EnableEvents = True End If End Sub Any ideas? Thanks! "Debra Dalgleish" wrote: You can do this with programming. There's a sample file he http://www.contextures.com/excelfiles.html Under PivotTables, look for 'Change Page Field' Kreed wrote: I was able to create a pivot table using an established pivot table; however, the established pivot table has filters (created using the PAGE area of the layout). I was hoping that when the established pivot table is filtered on (thus changing the data within the pivot), it would update the newly created pivot with the same filters. Is there something I should do that would acheive what I desire in this second pivot table? Thanks! -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
#4
|
|||
|
|||
pivot table created from another pivot table
If you provide more information on what you're trying to do, someone
might be able to help you adapt the code to your worksheet. Kreed wrote: This is very much on the track that I need to be on. From what I see in the sample file, it is in visual basic that I would need to do this programming. I can somewhat follow what programming; however, I have a few attributes in the Page field that I would need to link. What could I do with this programming to help me get there? Here is the programming that I copied: Option Explicit Dim mvPivotPageValue As Variant Private Sub Worksheet_Calculate() Dim wsOther As Worksheet Dim pt As PivotTable Dim pt1 As PivotTable Dim pt2 As PivotTable Dim strField As String Set wsOther = Sheets("Other Pivots") Set pt = Me.PivotTables(1) Set pt1 = wsOther.PivotTables(1) Set pt2 = wsOther.PivotTables(2) strField = "Market" If LCase(pt.PivotFields("Market").CurrentPage) < LCase(mvPivotPageValue) Then Application.EnableEvents = False pt.RefreshTable mvPivotPageValue = pt.PivotFields(strField).CurrentPage pt1.PageFields(strField).CurrentPage = mvPivotPageValue pt2.PageFields(strField).CurrentPage = mvPivotPageValue Application.EnableEvents = True End If End Sub Any ideas? Thanks! "Debra Dalgleish" wrote: You can do this with programming. There's a sample file he http://www.contextures.com/excelfiles.html Under PivotTables, look for 'Change Page Field' Kreed wrote: I was able to create a pivot table using an established pivot table; however, the established pivot table has filters (created using the PAGE area of the layout). I was hoping that when the established pivot table is filtered on (thus changing the data within the pivot), it would update the newly created pivot with the same filters. Is there something I should do that would acheive what I desire in this second pivot table? Thanks! -- 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 |
#5
|
|||
|
|||
pivot table created from another pivot table
Hi again,
I have a pivot table with four items in the "page area" on which users can filter to customize the report much like this: Market (All) City (All) Service (All) Price (All) Data Group Type Client Hours Revenue Group 1 Type 1 Client 1 5 100 Client 1 80 400 Client 1 100 500 Client 1 200 1,000 I would like to have it link to several other reports within the same workbook. Through my original question, I was directed to http://www.contextures.com/excelfiles.html, which help me somewhat link pivot table based on a string field. It worked wonderfully when I was using one attribute in the "page area" (for example "Market") but I would like to carry that functionality through the other three attributes of the "page area" (i.e city, service, and price). I included the programming found in the sample provided on http://www.contextures.com/excelfiles.html, if someone can help me go a step further by directing me to do the same functionality for several attributes found in the "page area", I would be most appreciative!! Also, do all the pivot table have to come from the same data source? Many thanks!! "Debra Dalgleish" wrote: If you provide more information on what you're trying to do, someone might be able to help you adapt the code to your worksheet. Kreed wrote: This is very much on the track that I need to be on. From what I see in the sample file, it is in visual basic that I would need to do this programming. I can somewhat follow what programming; however, I have a few attributes in the Page field that I would need to link. What could I do with this programming to help me get there? Here is the programming that I copied: Option Explicit Dim mvPivotPageValue As Variant Private Sub Worksheet_Calculate() Dim wsOther As Worksheet Dim pt As PivotTable Dim pt1 As PivotTable Dim pt2 As PivotTable Dim strField As String Set wsOther = Sheets("Other Pivots") Set pt = Me.PivotTables(1) Set pt1 = wsOther.PivotTables(1) Set pt2 = wsOther.PivotTables(2) strField = "Market" If LCase(pt.PivotFields("Market").CurrentPage) < LCase(mvPivotPageValue) Then Application.EnableEvents = False pt.RefreshTable mvPivotPageValue = pt.PivotFields(strField).CurrentPage pt1.PageFields(strField).CurrentPage = mvPivotPageValue pt2.PageFields(strField).CurrentPage = mvPivotPageValue Application.EnableEvents = True End If End Sub Any ideas? Thanks! "Debra Dalgleish" wrote: You can do this with programming. There's a sample file he http://www.contextures.com/excelfiles.html Under PivotTables, look for 'Change Page Field' Kreed wrote: I was able to create a pivot table using an established pivot table; however, the established pivot table has filters (created using the PAGE area of the layout). I was hoping that when the established pivot table is filtered on (thus changing the data within the pivot), it would update the newly created pivot with the same filters. Is there something I should do that would acheive what I desire in this second pivot table? Thanks! -- 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 |
#6
|
|||
|
|||
pivot table created from another pivot table
You could add more variables to the code, to accommodate the number of
page fields. I've added a sample file to my web site, that shows the code for two page fields: http://www.contextures.com/excelfiles.html Under PivotTables, look for 'Change Multiple Page Fields' Kreed wrote: Hi again, I have a pivot table with four items in the "page area" on which users can filter to customize the report much like this: Market (All) City (All) Service (All) Price (All) Data Group Type Client Hours Revenue Group 1 Type 1 Client 1 5 100 Client 1 80 400 Client 1 100 500 Client 1 200 1,000 I would like to have it link to several other reports within the same workbook. Through my original question, I was directed to http://www.contextures.com/excelfiles.html, which help me somewhat link pivot table based on a string field. It worked wonderfully when I was using one attribute in the "page area" (for example "Market") but I would like to carry that functionality through the other three attributes of the "page area" (i.e city, service, and price). I included the programming found in the sample provided on http://www.contextures.com/excelfiles.html, if someone can help me go a step further by directing me to do the same functionality for several attributes found in the "page area", I would be most appreciative!! Also, do all the pivot table have to come from the same data source? Many thanks!! "Debra Dalgleish" wrote: If you provide more information on what you're trying to do, someone might be able to help you adapt the code to your worksheet. Kreed wrote: This is very much on the track that I need to be on. From what I see in the sample file, it is in visual basic that I would need to do this programming. I can somewhat follow what programming; however, I have a few attributes in the Page field that I would need to link. What could I do with this programming to help me get there? Here is the programming that I copied: Option Explicit Dim mvPivotPageValue As Variant Private Sub Worksheet_Calculate() Dim wsOther As Worksheet Dim pt As PivotTable Dim pt1 As PivotTable Dim pt2 As PivotTable Dim strField As String Set wsOther = Sheets("Other Pivots") Set pt = Me.PivotTables(1) Set pt1 = wsOther.PivotTables(1) Set pt2 = wsOther.PivotTables(2) strField = "Market" If LCase(pt.PivotFields("Market").CurrentPage) < LCase(mvPivotPageValue) Then Application.EnableEvents = False pt.RefreshTable mvPivotPageValue = pt.PivotFields(strField).CurrentPage pt1.PageFields(strField).CurrentPage = mvPivotPageValue pt2.PageFields(strField).CurrentPage = mvPivotPageValue Application.EnableEvents = True End If End Sub Any ideas? Thanks! "Debra Dalgleish" wrote: You can do this with programming. There's a sample file he http://www.contextures.com/excelfiles.html Under PivotTables, look for 'Change Page Field' Kreed wrote: I was able to create a pivot table using an established pivot table; however, the established pivot table has filters (created using the PAGE area of the layout). I was hoping that when the established pivot table is filtered on (thus changing the data within the pivot), it would update the newly created pivot with the same filters. Is there something I should do that would acheive what I desire in this second pivot table? Thanks! -- 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 -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
#7
|
|||
|
|||
pivot table created from another pivot table
This is beautiful, thank you so much!!
"Debra Dalgleish" wrote: You could add more variables to the code, to accommodate the number of page fields. I've added a sample file to my web site, that shows the code for two page fields: http://www.contextures.com/excelfiles.html Under PivotTables, look for 'Change Multiple Page Fields' Kreed wrote: Hi again, I have a pivot table with four items in the "page area" on which users can filter to customize the report much like this: Market (All) City (All) Service (All) Price (All) Data Group Type Client Hours Revenue Group 1 Type 1 Client 1 5 100 Client 1 80 400 Client 1 100 500 Client 1 200 1,000 I would like to have it link to several other reports within the same workbook. Through my original question, I was directed to http://www.contextures.com/excelfiles.html, which help me somewhat link pivot table based on a string field. It worked wonderfully when I was using one attribute in the "page area" (for example "Market") but I would like to carry that functionality through the other three attributes of the "page area" (i.e city, service, and price). I included the programming found in the sample provided on http://www.contextures.com/excelfiles.html, if someone can help me go a step further by directing me to do the same functionality for several attributes found in the "page area", I would be most appreciative!! Also, do all the pivot table have to come from the same data source? Many thanks!! "Debra Dalgleish" wrote: If you provide more information on what you're trying to do, someone might be able to help you adapt the code to your worksheet. Kreed wrote: This is very much on the track that I need to be on. From what I see in the sample file, it is in visual basic that I would need to do this programming. I can somewhat follow what programming; however, I have a few attributes in the Page field that I would need to link. What could I do with this programming to help me get there? Here is the programming that I copied: Option Explicit Dim mvPivotPageValue As Variant Private Sub Worksheet_Calculate() Dim wsOther As Worksheet Dim pt As PivotTable Dim pt1 As PivotTable Dim pt2 As PivotTable Dim strField As String Set wsOther = Sheets("Other Pivots") Set pt = Me.PivotTables(1) Set pt1 = wsOther.PivotTables(1) Set pt2 = wsOther.PivotTables(2) strField = "Market" If LCase(pt.PivotFields("Market").CurrentPage) < LCase(mvPivotPageValue) Then Application.EnableEvents = False pt.RefreshTable mvPivotPageValue = pt.PivotFields(strField).CurrentPage pt1.PageFields(strField).CurrentPage = mvPivotPageValue pt2.PageFields(strField).CurrentPage = mvPivotPageValue Application.EnableEvents = True End If End Sub Any ideas? Thanks! "Debra Dalgleish" wrote: You can do this with programming. There's a sample file he http://www.contextures.com/excelfiles.html Under PivotTables, look for 'Change Page Field' Kreed wrote: I was able to create a pivot table using an established pivot table; however, the established pivot table has filters (created using the PAGE area of the layout). I was hoping that when the established pivot table is filtered on (thus changing the data within the pivot), it would update the newly created pivot with the same filters. Is there something I should do that would acheive what I desire in this second pivot table? Thanks! -- 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 -- 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 | |||
updating pivot table to include additional rows | Excel Discussion (Misc queries) | |||
How to get pivot table Time field to appear correctly | Excel Worksheet Functions | |||
Change Data In Pivot Table | New Users to Excel | |||
Pivot table, dynamic data formula | Excel Discussion (Misc queries) | |||
Pivot Table Problems | Excel Discussion (Misc queries) |