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?



0
ExcelGuRu (23)
5/2/2005 8:37:23 PM
excel.misc 78881 articles. 5 followers. Follow

3 Replies
554 Views

Similar Articles

[PageSpeed] 32

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 here:

   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

0
dsd1 (5911)
5/3/2005 2:34:54 AM
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 here:
> 
>    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
> 
> 
0
ExcelGuRu (23)
5/3/2005 5:19:07 PM
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 here:
>>
>>   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

0
dsd1 (5911)
5/3/2005 9:45:02 PM
Reply:

Similar Artilces:

double clicking and draging a column in a chart to chg data
in Excel 2003, double clicking on a column in a chart and then dragging the column up or down would change the data in a table upon which the chart depended. How does one do this in excel 2007? Tom Hi, That feature has been removed in 2007, there is no way to do it. If this helps, click the Yes button. -- Thanks, Shane Devenshire "Tom of inns" wrote: > in Excel 2003, double clicking on a column in a chart and then dragging the > column up or down would change the data in a table upon which the chart > depended. > > How does one do this in excel 2007? &g...

Problems migrating BCM data into CRM SB edition
Hi There I am having a problem migrating data from Business Contacts Manager (BCM) into CRM 3.0 Small Business edition. I have downloaded the BCM data migration pack and have followed the data migration documentation to the letter. I even cleaned up the BCM database prior to copying the files, checking them for errors using the Manage Database option in the Business Tools menu. It gets so far through the migration process and then bombs out. Here is the final few entries from the log file: 28/10/2006 12:18:53------>Transitioning to next screen. From: ConfigurationSummary screen. To: ...

transfer data from multiple columns to singlr column
I have data in form a d g b e h c f i (but larger scale) and I need it in a single column going a to z. Hi, highlight you data, copy, go to the column where you want to see the data, paste special, transpose "lc85" wrote: > I have data in form a d g > b e h > c f i (but larger scale) > and I need it in a single column going a to z. You up for using a macro? Sub ToOneColumn() 'dantuck Mar 7, 2007 &...

Dynamic Xml editor
Hi, I am writing a user control that will help a user that know (almost) nothing about Xml to build a Xml file based on a Xsd schema that is given to the user control. The idea is to have a treeview on the left and a property grid on the right. The treeview is loaded with the root elements from the schema. When the user right click on a node, a context menu allow him to add subnodes if schema allow it. This way, the user can create items, attributes, ... The property grid is filled by the user with data. At the end, the Xml file is generated and validated. My problem is about the design....

PA Misc. Log Tables
Hi, How can I find the difference between Billed and Un-billed Miscelleneous Logs? Where Can I see Un-billed Misc. Logs and which tables. Hari I have a spreadsheet with the PA tables listed in it. Send an email message to charles.zarzour@intergraph.com and I will send you the spreadsheet. I do an excel spreadsheet for each Dictionary. Makes it easy to lookup tables. "Hari" wrote: > Hi, > How can I find the difference between Billed and Un-billed Miscelleneous > Logs? > Where Can I see Un-billed Misc. Logs and which tables. > > Hari > > Can I ...

visual basic
Hi, I trying to retrieve values from a table to calculate the 14days average value of a stock closing price. However, i encounter some problem as stated beside the code as follows: Function DaysAvgs() 'Calculate the average value of a given value. Dim db As DAO.Database Dim rst As DAO.Recordset Dim varBookmark As Variant Dim numAve, numDaysAvg As Double Dim intA, intB, lngCount As Integer Set db = CurrentDb 'Open Table Set rst = db.OpenRecordset("SGX Individual Historical", dbOpenTable) rst.MoveFirst Do While Not rst.EOF intA = 1 intB = 0 varBookmark = rst.Bookmark n...

Can't open 2005 data file after reinstalling Money 2005
I am experiencing a recurring problem. I have had to reinstall Windows XP and MS Money 2005. I am now unable to open my previously converted 2005 file or restore any backup version. I consistently get the following error message: "Money cannot locate filename or cannot open it, possibly because it is a read-only file, you do not have permission to change it, or your disk drive is write- protected. If you have chosen the correct file and it cannot be accessed, you will need to click OK and then Restore your most recent backup file." Any help or thoughts would be greatly ap...

Joining text with a formula in cell #4
just to complete the thread... I found the answer. You have to change the format of the cell to custom 0.00"*" this is the only way it will show only 2 decimal places Thanks for the hel -- Mustard Hea ----------------------------------------------------------------------- Mustard Head's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1630 View this thread: http://www.excelforum.com/showthread.php?threadid=27700 ...

Macro for Sharepoint List and another Table?? Same # records?
I am creating a database which we have a form made up of prepopulated data (from a sharepoint list) and also data input needed by a user (for example, comments, etc.). I have two tables: one sharepoint list that is linked to sharepoint website and another table that houses the additional user inputs [table 2]. I have linked the files based on the primary key. So, every record in my table 2 has a plus sign and it will expand and you will see the linked data from the sharepoint list. Is there a way that I can automatically create a record for every corresponding record ...

Extending formulas
Subject: Extending formulas Hi, For my application that uses Excel for calculations. I need to be able to extend the forula base of Excell with complex scientifc functions. Is there a way to add new functions to the Excel function base? Thanks Spx. MS has provided Visual Basic for Applications (VBA) to customize Excel with new functions, commands, forms, menus, etc. Tools|Macro|Visual Basic Editor From the VBA editor Insert Module Then write your functions in VBA. Details of writting functions in VBA is a very big topic, http://www.fontstuff.com/vba/vbatut01.htm may help y...

Copying Data in a cell in one sheet to a cell in another sheet
I've run into a problem trying to copy data from a cell in one sheet to another. I have a spreadsheet called "rating" which contains a number of formula that calculates a final number. I also have a spreadsheet called "Final" that copies over the information from "Rating". In "Final", I'm trying to copy a number from "Rating" into a particular cell. I put in =Rating! G89, but it won't work. When I press enter, a window pops up "Update value:Rating". I press enter again and in the cell where I want the number ...

Customer check data
When customers pay by check RMS asks for specific information such as drivers license number, routing number, account number, address and phone number. Does anyone have a report and or a way to extract this info from the database for cases when the check is returned for NSF? Please advise, Scott We can write you this report. Contact me for detail on price . Afshin Alikhani - [ afshin@retailrealm.co.uk ] CEO - Retail Realm = = = = = = = = = = "Scott Santorio" <scott@tt-newyork.com> wrote in message news:e8ZKkR6$HHA.3716@TK2MSFTNGP03.phx.gbl... > When customers pa...

Macro to seperate data
Hi I seem to be struggling to find a macro that will work in previous threads. In sheet 1 is a list of data in columns A:N and the number of rows will vary. It is a list of sales with each sale record ocuppying one row. The salesperson's name is in column C and each salesperson will have multiple entries. What I am trying to do is create a seperate summary sheet in the workbook for each salesperson. Therefore sheets 2 to 20 are templates that already exist with a different salesperson's name entered into cell C3 on each of them. I am trying to find a macro that ...

Need HELP! for Linking data
Could someone please direct me to where I can learn how to link date in a work book. i.e., I have individual pages for each subject but I need the data that is entered in these individual pages to transfer to the Master page without having to manually in put it.........TNX Bubey, There are not too many bits about linking worksheets or workbooks that I can find. But have a look at the links below, in case they give you the information you need. I think it is frustratingly one of those things which is very easy when you know how, or if you can get someone to actually show you, but if you hav...

Need to add to current formula
I have this formula that will cause values to change based on the mont that is referenced in the formula ($L$1). Currently the formul is:=VLOOKUP($A$1,$AD$7:$AG$44,IF($L$1="January",2,IF($L$1="February",2,IF($L$1="March",2,IF($L$1="April",2,IF($L$1="MAY",4,IF($L$1="June",3,IF($L$1="July",3,0))))))),0) I need to add August, September, October, November, & December to thi formula but excel is not allowing me. Does anyone know how I can get around this? Oh by the way November thru April =2, May and October=4 and June thr...

Can i use conditional formating on a cell when it contains a formula?
I am trying a "conditional formatting" on a cell that contains formula, but it didn't work. "If cell value is equal to 0 then font - white" This doesn't work, stays always. If i use this condition on a cell without formula it works just fine. Thank -- si ----------------------------------------------------------------------- sit's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=262 View this thread: http://www.excelforum.com/showthread.php?threadid=26784 Hi are you sure your formula returns an exact zero?. Could you post the formul...

Macro
I need a macro that help me to transfer name and address information from an specific table in excel to a template in words on specific areas and then print the word document. The reason for this is that i need to create diferents letters to be sent to the customers from the excel table. Example of the table is: soc seg, customer name, child name, customer code, add 1 , add2, city, estate, zip code. all this information will be paste on word letter template on specific areas or fields. Any suggestion!!! -- nicoro Hi IMHO the best approach would be to set up a mail merge documen...

macros entering data
How do I create a macro that goes to one cell then waits until I enter new data, then goes to another cell and waits until I enter new data etc? thanks How about something like sub Enter_Data() dim NewValue NewValue = inputbox("Enter the value for cell A1: ") range("a1").value = NewValue NewValue = inputbox("Enter the value for cell G2: ") range("g2").value = NewValue NewValue = inputbox("Enter the value for cell I8: ") range("i8").value = NewValue end sub ...

item class table
I am creating SOP IM import. I need to fill the distribution fields with a rev account that is part of the item class. I would like to find a table that would hold the item class accounts. I looked in IV40400 and did not see any distribution accounts. What is the best table to pull these accounts. If the accounts have been defined on the Item Class, they will appear on the records in the IV40400 table. They're in the fields IVIVINDX, IVIVOFIX, etc - and they're just the keys to the actual account definitions in the GL00100 table. If a particular account type isn't defined ...

Cell with large amount of data not showing all data
I'm running Excel 97. I have a cell with 358 words (1928 characters with spaces). Word wrap is on for the cell. Only part of the text is displayed even though the cell is big enough to show everything. If I make the cell wider (wider than a page) more of the text shows but not everything. I tried a new worksheet with the same text and had the same problem. Is this a known issue with excel? Is there a solution? Thanks, Brad Left to its own devices, excel will only show about 1000 characters in a cell. But you can add some alt-enters (to force a new line within the cell) and see more s...

Adding blank "separator" Rows in a Pivot Table
Does anyone know how to insert blank rows into a pivot table (say, between groupings, etc.)? There has to be a way, since some of the various table formats that come with Excel include blank rows. Double-click the field button that you want to format Click Layout Add a checkmark to Insert blank line after each item Click OK, twice Bucko wrote: > Does anyone know how to insert blank rows into a pivot table (say, > between groupings, etc.)? There has to be a way, since some of the > various table formats that come with Excel include blank rows. -- Debra Dalgleish Contextures http...

Formula Problem?
I am using Excel 2000 with Windows XP. I am having a problem. I am on Sheet 2 of my workbook. I have SSN on a sheet named Employees in the same workbook. I need to take the numbers on the Employees Sheet and transfer it to the sheet 2. I know how to do this. It just won't work. This is a copy of my formula. =SUM(Employees!C3) This should take the SSN that is in the C3 cell on the employees sheet and place it at the cell where the formula is typed. When I put this formula in the cell I am getting just a "0". Please help. =Employees!C3 -- Kind regards, Niek Otten...

formula auditing/macro
Can anyone give me the sytax to goto - special - precedents so I can create a macro so I can assign to a hotkey and dont have to go through 4 steps ? Thanks, Yosef With A1=1 and D2=2*A1, and D1 as active cell: I recorded a macro for these steps: Edit|GoTo->Special->Precedence And the macro contained just one line: Selection.DirectPrecedents.Select best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "ynissel" <ynissel@discussions.microsoft.com> wrote in message news:DA544BDE-3717-4953-A5E3-06191BC28373@microsoft.com... > Can anyone...

Start macro creating a mail with contact data and autotext
Hallo, I am working with an user form. The developing of that form started with Outlook XP with a lot of code inside for different buttons. I changed to Outlook 2007 and unfortunately the code of the form was not longer displayed. What I learned about this is that MS does not support to much code in the form (or maybe a bug). They also do not support any longer. I was sending this form to MS support but they told it is do much code inside and they do not know, why the code is not displayed. In Outlook 2003 the code is displayed as in Outlook XP. Because I do not know real...

find data and autopaste when found
Hi, Can someone help me how to do this : For checken the backorders of our customers we can extract a list fro our SAP system. this list is always different and shows us ever product per customer in Back order. ex. Customer A has product 1 en in backorder. This gives 2 lines in the xls file. can excel put th name of the customer on a form and it's backorders automatically. Ca it create for each customer showing in the list a new form? thanks koenraa -- Message posted from http://www.ExcelForum.com ...