Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Excel GuRu
 
Posts: n/a
Default Pivot table, dynamic data formula

I set up a dynamic formula as noted on the Contextures website
<http://www.contextures.com/xlPivot01.html#Dynamic . I have multiple pivot
tables in my worksheet. I would like to set up the pivot tables so they
refer to the 1st pivot table, which references the dynamic formula range. In
the pivot table wizard,when I change the data source for each of the pivot
tables to the 1st pivot table, click "finish" and go back into the wizard to
see what data the table is referring to, it always comes up with the dynamic
formula, not the 1st pivot table.

I believe the drawback is that you have to refresh each pivot table
individually. Is there anyway around the issue? We are rolling out a sales
reporting tool and believe the salespeople will get confused if they have to
refresh the query and every single pivot table(there are many). Is there a
way to refresh multiple pivot tables with one click?



  #2   Report Post  
Debra Dalgleish
 
Posts: n/a
Default

If the pivot tables are based on the same pivot cache, when you refresh
one, all will be refreshed. To base a new pivot table on an existing on,
choose 'Another PivotTable Report or PivotChart Report' in step1 of the
pivottable wizard.

To determine which pivot cache was used for each pivot table, you can
use a User Defined Functions, as described he

http://www.contextures.com/xlPivot11.html

Excel GuRu wrote:
I set up a dynamic formula as noted on the Contextures website
<http://www.contextures.com/xlPivot01.html#Dynamic . I have multiple pivot
tables in my worksheet. I would like to set up the pivot tables so they
refer to the 1st pivot table, which references the dynamic formula range. In
the pivot table wizard,when I change the data source for each of the pivot
tables to the 1st pivot table, click "finish" and go back into the wizard to
see what data the table is referring to, it always comes up with the dynamic
formula, not the 1st pivot table.

I believe the drawback is that you have to refresh each pivot table
individually. Is there anyway around the issue? We are rolling out a sales
reporting tool and believe the salespeople will get confused if they have to
refresh the query and every single pivot table(there are many). Is there a
way to refresh multiple pivot tables with one click?





--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

  #3   Report Post  
Excel GuRu
 
Posts: n/a
Default

Debra, eventually, I will just go to your site when I need help. I don't
always match the solution I need to the descriptions on your site, but
quickly after I post an item I'm directed to your site again.

I think I've used almost every topic you've posted on pivot tables. Thank
you so much.

"Debra Dalgleish" wrote:

If the pivot tables are based on the same pivot cache, when you refresh
one, all will be refreshed. To base a new pivot table on an existing on,
choose 'Another PivotTable Report or PivotChart Report' in step1 of the
pivottable wizard.

To determine which pivot cache was used for each pivot table, you can
use a User Defined Functions, as described he

http://www.contextures.com/xlPivot11.html

Excel GuRu wrote:
I set up a dynamic formula as noted on the Contextures website
<http://www.contextures.com/xlPivot01.html#Dynamic . I have multiple pivot
tables in my worksheet. I would like to set up the pivot tables so they
refer to the 1st pivot table, which references the dynamic formula range. In
the pivot table wizard,when I change the data source for each of the pivot
tables to the 1st pivot table, click "finish" and go back into the wizard to
see what data the table is referring to, it always comes up with the dynamic
formula, not the 1st pivot table.

I believe the drawback is that you have to refresh each pivot table
individually. Is there anyway around the issue? We are rolling out a sales
reporting tool and believe the salespeople will get confused if they have to
refresh the query and every single pivot table(there are many). Is there a
way to refresh multiple pivot tables with one click?





--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html


  #4   Report Post  
Debra Dalgleish
 
Posts: n/a
Default

You're welcome! Thanks for letting me know that the information on my
site has helped you.

Excel GuRu wrote:
Debra, eventually, I will just go to your site when I need help. I don't
always match the solution I need to the descriptions on your site, but
quickly after I post an item I'm directed to your site again.

I think I've used almost every topic you've posted on pivot tables. Thank
you so much.

"Debra Dalgleish" wrote:


If the pivot tables are based on the same pivot cache, when you refresh
one, all will be refreshed. To base a new pivot table on an existing on,
choose 'Another PivotTable Report or PivotChart Report' in step1 of the
pivottable wizard.

To determine which pivot cache was used for each pivot table, you can
use a User Defined Functions, as described he

http://www.contextures.com/xlPivot11.html

Excel GuRu wrote:

I set up a dynamic formula as noted on the Contextures website
<http://www.contextures.com/xlPivot01.html#Dynamic . I have multiple pivot
tables in my worksheet. I would like to set up the pivot tables so they
refer to the 1st pivot table, which references the dynamic formula range. In
the pivot table wizard,when I change the data source for each of the pivot
tables to the 1st pivot table, click "finish" and go back into the wizard to
see what data the table is referring to, it always comes up with the dynamic
formula, not the 1st pivot table.

I believe the drawback is that you have to refresh each pivot table
individually. Is there anyway around the issue? We are rolling out a sales
reporting tool and believe the salespeople will get confused if they have to
refresh the query and every single pivot table(there are many). Is there a
way to refresh multiple pivot tables with one click?





--
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
Pivot table - printing specific data Doug Excel Discussion (Misc queries) 1 April 24th 05 08:29 PM
grouping data in a pivot table William Excel Discussion (Misc queries) 2 April 6th 05 09:16 PM
Pivot Table Source Data ABH New Users to Excel 1 February 26th 05 01:10 PM
Pivot Table Customize functions in the Data Field PSKelligan Excel Discussion (Misc queries) 2 January 4th 05 07:51 PM
Data Table - Does it work with DDE links and stock tickers? Post Tenebras Lux Excel Discussion (Misc queries) 0 December 1st 04 06:15 PM


All times are GMT +1. The time now is 03:10 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"