Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Kreed
 
Posts: n/a
Default 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   Report Post  
Debra Dalgleish
 
Posts: n/a
Default

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   Report Post  
Kreed
 
Posts: n/a
Default 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   Report Post  
Debra Dalgleish
 
Posts: n/a
Default 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   Report Post  
Kreed
 
Posts: n/a
Default 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   Report Post  
Debra Dalgleish
 
Posts: n/a
Default 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   Report Post  
Kreed
 
Posts: n/a
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
updating pivot table to include additional rows Ellen Excel Discussion (Misc queries) 8 July 15th 08 01:33 PM
How to get pivot table Time field to appear correctly wccmgr Excel Worksheet Functions 1 August 23rd 05 12:26 AM
Change Data In Pivot Table John Calder New Users to Excel 1 July 7th 05 10:41 PM
Pivot table, dynamic data formula Excel GuRu Excel Discussion (Misc queries) 3 May 3rd 05 10:45 PM
Pivot Table Problems Rachel Gonsior Excel Discussion (Misc queries) 3 March 21st 05 08:24 PM


All times are GMT +1. The time now is 07:34 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"