pivot table changes when data is refreshed.

Hi, 
I have a pivot table with grouping based on a field called "period
which is of date type. I have grouped it into months and quarters
However when i refresh the pivot table whenever the data (which is in 
seperate sheet) is changed, the groupings are going out. I want t
preserve the groupings as they are like a template. How do i stop th
changing the format  and layout of the pivot table whenever i refres
the data? any ideas? please help.
regards
Kiran:mad

-----------------------------------------------
~~ Message posted from http://www.ExcelTip.com
~~View and post usenet messages directly from http://www.ExcelForum.com

0
11/26/2003 7:02:29 PM
excel.misc 78881 articles. 5 followers. Follow

1 Replies
561 Views

Similar Articles

[PageSpeed] 38

Intentionally or otherwise, XL does not retain all of the custom 
formatting applied to a PivotTable.  Two documented examples are:

XL2000: PivotTable Does Not Preserve Conditional Formatting
http://support.microsoft.com/default.aspx?scid=kb;en-us;215986

XL97: Formatting Not Retained in PivotTable
http://support.microsoft.com/default.aspx?scid=kb;en-us;162028

You might find more information by searching the MSKB.

-- 
[Posted directly to the Usenet newsgroup -- no affiliation with 
Excelforum]

Regards,

Tushar Mehta, MS MVP -- Excel
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article <isskiran.xim2a@excelforum-nospam.com>, 
isskiran.xim2a@excelforum-nospam.com says...
> 
> Hi, 
> I have a pivot table with grouping based on a field called "period"
> which is of date type. I have grouped it into months and quarters.
> However when i refresh the pivot table whenever the data (which is in a
> seperate sheet) is changed, the groupings are going out. I want to
> preserve the groupings as they are like a template. How do i stop the
> changing the format  and layout of the pivot table whenever i refresh
> the data? any ideas? please help.
> regards
> Kiran:mad:
> 
> 
> ------------------------------------------------
> ~~ Message posted from http://www.ExcelTip.com/
> ~~View and post usenet messages directly from http://www.ExcelForum.com/
> 
> 
0
11/30/2003 2:09:49 PM
Reply:

Similar Artilces:

Changing vat rate calculation in Money 2006
I have enabled VAT calculations on my transactions in my accounts and the tax rate has changed but I can't find a way to change it in Money 2006. This is a database that has been upgraded over many versions and I'm sure I've changed this before in another version. I can see the old rate in some reports I print but for the life of me I can't find a setting in 2006. Anyone know of the way to change the Vat Rate? or is this another new feature ;(( -- Boyd In microsoft.public.money, Boyd Tucker wrote: >I have enabled VAT calculations on my transactions in my accou...

How do I change the activity preview pane?
For Phone and Task Preview forms, I have tried to add the Description field to the form, but I am not able to make the field span two columns. Does anyone know a way around this? We need a better way to quickly jump between activity information while we're on the phone with a contact. Should we be storing this type of information somewhere else? Our sales team is finding this a barrier and thinks MS CRM is nothing more than a glorified address book. They are pushing us to move to ACT, but I think it’s just a matter of using this system more efficiently. Thanks for your help/inp...

Pivot table and percentage
I have an excel spreadsheet with the following information: SCHOOL, PERSON, SUBJ, COURSE_TYPE, COURSE, SECTION, TERM, OUTSIDE_RANGE AA, John Doe, Math, 1U, 101, 01, 200508, 0 AA, John Doe, Math, 1U, 102, 01, 200508, 1 AA, John Doe, Math, 1G, 601, 70, 200508, 0 AA, John Doe, Math, 1U, 101, 01, 200601, 0 AA, John Doe, Math, 1U, 101, 02, 200601, 1 AA, John Doe, Math, 1U, 101, 03, 200601, 1 AA, John Doe, Math, 1U, 101, 01, 200608, 0 AA, John Doe, Math, 1U, 102, 01, 200608, 1 AA, John Doe, Math, 1U, 103, 01, 200608, 0 I want to be able to have in a pivot table the following: Page TERM Rows SCHO...

changing Exchange primary windows account
How To change primary Windows Account in exchange 5.5 mailbox, I have 3000 mailboxes in exchange 5.5 server. I want to change the primary windows account of all the users. can anybody suggest the faster way to do it.does anybody hv any tool. http://support.microsoft.com/default.aspx?scid=kb;en-us;155414 Sri wrote: > How To change primary Windows Account in exchange 5.5 > mailbox, I have 3000 mailboxes in exchange 5.5 server. > I want to change the primary windows account of all the > users. > can anybody suggest the faster way to do it.does anybody > hv any tool. ...

Formatting Charts from Pivot Table...
I've created a pivot table/with chart. The default chart was a bar chart, which I've changed to a pie chart. The problem is I don't like the default colors so I've changed them to the way I like them; however, once I save the document they go back to the default colors. Is there any way to keep the revised colors, rather than the default colors? Thanks, KSL. ...

Changing values in a value box
Hi all, I have a data set that looks like this: DATE A$ B$ A% B% mar-14 50 100 33 66 mar-21 100 100 50 50 mar-28 75 25 75 25 I want to chart the percentages by each date, but I want the "value" box to display the dollars. The only way I can think to trick the Excel chart is create 2 x-axises and hide the $ axis, but then the $ values don't match the height of the % columns. Is there a way to link the $ values to the % columns? Thanks. Josh, To chart only the percentages, select the Date column, and also the two % columns (drag throug...

Changing the Organization Name
I need to change my organization name in CRM, my license keys are registered under my Full Company Name and CRM was installed without the LLC. Note: The organization name created in the previous procedure (setting up CRM) must match the organization specified when your license key was obtained. When you do the install, the Org Name you specifiy needs to match the Org Name that was used for the License Keys. So, in your case, just install it w/o the LLC (assuming that's what your license keys are setup for). The v1.2 install shouldn't let you by the screen where you enter this ...

Charting data as 'boxes'
Hi All I have an x-y chart (y is a date axis) and a series of events happening along the x-axis. The x-axis is the position along a datum, and I'd like to show a 'box' starting at one x value through to a second x-value to define where the event is happening; then the top and bottom of the box define the start and finish dates. I can store the data in a table easily enough, [ID;Startx;Endx;Starty;Endy] but is it then possible to use it to define the four corners of a box and get it to draw a series of 'boxes' on a chart ?? Hope this makes sense ! Regards John P J...

Need rotation schedule with 6 tables of 6 people for all to meet o
I am trying to develop an algorithm for a networking session. I need a rotation schedule so that everyone will get to meet everyone only once. I will have 6 tables of 6 people at each table for a total of 36 people. I have the tables lettered A B C D E F and 1-36 people numbered and want to rotate everyone through so they all get to talk to each other just once. On Fri, 13 Apr 2007, in microsoft.public.excel.charting, sccyclist <sccyclist@discussions.microsoft.com> said: >I am trying to develop an algorithm for a networking session. I need a >rotation schedule so that eve...

Changing a custom field length
We have created a custom section and there is a field (in that custom section) whose length has to be changed from 100 to 500 of nvarchar datatype, but the interface does not allow more than 100 for nvarchar. I couldnt modify the format of textbox to textarea as it is disabled nor i can change the datatype to another datatype. can anybody please help me with this?? Affy Unfortunately you'll have to recreate the field, as you can neither increse the length of a text field nor change it's format after it's been created. Then there's a question of what to do with any existing...

Transfer form data
In access 2000, how do I transfer form data to MS Word?. I would like to use something like transferSpreadsheet which I use often to transfer data to Excel. I would prefer not to create an Access report and then use "Publish with MS Word". This method does NOT faithfully reproduce the report. Thanks -- Shell "Shell" wrote: > In access 2000, how do I transfer form data to MS Word?. I would like to use > something like transferSpreadsheet which I use often to transfer data to > Excel. > > I would prefer not to create an Access report and then us...

Pivot Table with calculated Item
I have inserted a simple calculation item in my pivot table and now I have lots of rows with zero values, that don't relate to the parent feilds. My table shows total qty ordered in Qtr1 2004 and Qtr1 2005 grouped by Sales Rep, Customer, Product Family, and item code. Once the calc items were inserted, each SalesRep has an entry for every customer, which has an entry for every Prod Family, which would have an entry for every item (if it didn't exceed the limitations). Can anyone help me remove all these extraneous entries?? GD ...

Get/Change operation on table 'SY_Error_Messages_MSTR'
Hi all, We upgraded from GP version 9 to version 10 over the past weekend. I have a user who is entering Payables Transactions and she is receiving the following message when she tries to enter a duplicate PO#.... Unhandled database exception: A get/change operation on table 'SY_Error_Messages_MSTR' cannot find the table. Invalid object name 'SY01700' So based on the error, the incorrect message is appearing to the user. Can I rebuild the SY01700 table? Is this really the issue? Any help would be greatly appeciated! Thanks, Lori The SY01700 Table holds the error mess...

Data Markers
Can I change data markers so that it reflects the conditional formatting on that cell? For example if the data in column c is being plotted and the conditional formatting I have on the cells are if column D = "Y" can I have it so that those markers are plotted with a circle marker and the rest with a square marker? Thanks! Hi, Have a look at Jon's page for conditional charts. http://peltiertech.com/Excel/Charts/ConditionalChart1.html Cheers Andy ivy_gayle wrote: > Can I change data markers so that it reflects the conditional formatting on > that cell? For exampl...

How can i stop same data being repeated in a column
I have a list of contract numbers relating to application numbers or payments. they are in the format nnnnnnan or nnnnnnpn. The columns are fixed to this format only. If they are entered with the a or p in the wrong place or if they have been left out completely an error message will appear to alert the user. I want to know how to alert the user if they enter an application or a payment number that has already been entered. ie if they enter 022079a4 but that same application has been entered else where in the column. Hope you can help Ru Hello See Chip Pearson's http://www.cpearson....

Change in paste behaviout between Publisher 2000 and 2002-
I also have the same problem. I use publisher to print ID cards and every day paste about 100 images from a graphics program into the same sized text box in Publisher. With all versions of Publisher up to and including 2000 the pasted image would resize itself to the text frame. This being what I need. With Publisher 2002 onwards the pasted image pastes itself into a new frame on top of the existing image and much larger. I then have to delete the existing image and resize the new image, which takes much much longer than using Publisher 2000. If Microsoft provided an option somewhere to cho...

Error
My HR users are seeing a new error when trying to update one of the fields on the "Position Extra Fields" Screen (located at "Position Setup" Screen -> "Position Extra Fields" Screen) The error is "This employee record has been changed since you opened this window. Changes won't be saved." I can duplicated it also, even when signed in as admin. I tried tracing it but see nothing that helps. A SQL trace shows an attempted insert to HRPOS022 then next a Select to Dynamics..Sy01700 (holds the error messages) Ca...

Problems XSLT Transformation of XML Data to EXCEL 2000
Does Office Excel 2000 support XSLT transformation of data from XML cause am having problem when the attachment is opened in the client side if the Excels version is in Office 2000 and also its works fine if the Office Excel Version 2002 and up when attachment file is open via browser the data is in a single line of string in the first row of the excel worksheet Glenn Gomez wrote: > Does Office Excel 2000 support XSLT transformation of data from XML cause am > having problem when the attachment is opened in the client side if the Excels > version is in Office 2000 > >...

Pivot Table with Where clause?
Hi all. Hope you're having a great holiday! But back to business :). Let's say I have a worksheet with three columns: Employee, Assignment Name, and Complete (Y/N). There is a one-to-many relationship between Employee and Assignment Name. I understand how to make a Pivot table to show the total number of assignments for each employee, but how would I make a pivot table showing for each employee the number of assignments where Complete = Y? Thanks! Ken Add the Complete field to the Page area of the pivot table. From the dropdown, choose Y, and the pivot table will be filtered f...

How to read B-LOB data from Oracel DB using Pro*c
Hello, I'm trying to read B-LOB data in Oracle Database using Pro*c. I know how to read it with char mode. What I want to know is read it with binary mode. Are there any good person help me? Let me know if you have a good sample source. Thanks and have a nice day. ...

POS Extended Amount Flips to Positive on a return when changed?
Microsoft Retail Manager Version 1.3.1002 Hi, I recently had a problem balancing when i noticed that a return i processed was incorrectly journaled. Generating a detailed sales report also illustrated the same error. The quantity sold was -1, the sold price was -ve but the total sale was +ve, whereas when correct the quantity sold is -1 the sold price is +ve and the total sale is -ve. How This Happened: In POS I processed the return in what I thought was the usual manner. I pressed the return button and then because the item being returned had a reduced value I changed the extended price th...

Migrate data from 1.2 to 3.0
Hi Can i use the data migration framework to migrate data from 1.2 SBS to 3.0 Professional after doing all the required mappings ? Is there an easier way to have data from 1.2 SBS moved to 3.0 Professional? Unfortunately you cannot move data between deployments. You can only upgrade the existing CRM 1.2 deployment to 3.0. There is no 1.2 SBS Edition, so you may have to redeploy the 1.2 environment to a new place, then upgrade the system. -- Laszlo Kovacs MCSE+S, MCT, Microsoft Dynamics Certified Master "Jij" wrote: > Hi Can i use the data migration framework to migrate d...

I need an Excel Genius !!!!!!!!!!!!!!! Headers/Footer Changes
I think I need some work around code. I have two workbooks named QG and Add-In. Add-In contains all the code to manipulate data in QG. I have two option buttons in Sheets("QUOTE") in QG. Each option button represents a company. When an option button is clicked the code below located in Add-In is called, which changes the Header and Footer to represent that company. The code works fine, but there is a fault. If the Logo is moved to a different folder the code fails. Is there a way to store the two company logos (.jpeg) in one of the workbooks so that when one...

fast increasing database with only tables
I made an application in msaccess97 and converted this to access2000, this is the front end. The backend is still an access97 database (this is because I found some indexing problems in an access2000 database and this seems to be working perfect) The database is in use by approximately 25 persons at the same time for about 1.2 year now. The size of the database stayed about the same for a period of 1 year. But recently the backend grows very fast (from 200MB to 1000Mb in one week time) every time it is compressed, its size is reduced again. To find the reason for this I compacted the da...

Dates and balances don't change after downloads
I am using Money 2005 and I have five accounts (three bank accounts and two credit card accounts) from which I download financial data. Downloading financial data gives me no problem but the balance and dates of four of the five accounts don't change: they all sit on the same date with the time as midnight and the balance as it was in October or November. How do I fix this? Salvage tells me the files are fine and I'm at a loss. Steven ...