Pivot Table Dates

I'm bringing in data from an external database & creating a Pivot table with
grouped dates (Eg months)

when this table is copied and the date grouping changed from say months to
qtrs it changes the original to qtrs as well

is it possible to avoid this happening without creating seperate workbooks

Many thanks

Neil Thompson






0
2/1/2005 4:59:10 PM
excel 39879 articles. 2 followers. Follow

2 Replies
692 Views

Similar Articles

[PageSpeed] 34

You can always create another Pivot table within the same workbook, but not
link it to the existing one,  You will pay the price in terms of filesize,
but it will fix your problem.

-- 
Regards
           Ken.......................    Microsoft MVP - Excel
              Sys Spec - Win XP Pro /  XL 97/00/02/03

----------------------------------------------------------------------------
                  It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------

"Neil Thompson" <neilthompson@eircom.net> wrote in message
news:MxOLd.46730$Z14.30316@news.indigo.ie...
> I'm bringing in data from an external database & creating a Pivot table
with
> grouped dates (Eg months)
>
> when this table is copied and the date grouping changed from say months to
> qtrs it changes the original to qtrs as well
>
> is it possible to avoid this happening without creating seperate workbooks
>
> Many thanks
>
> Neil Thompson
>
>
>
>
>
>


0
ken.wright (2489)
2/1/2005 6:31:39 PM
Just to clarify - use the same source data but when it prompts you and asks
if you want to link to an existing table just say no.

-- 
Regards
           Ken.......................    Microsoft MVP - Excel
              Sys Spec - Win XP Pro /  XL 97/00/02/03

----------------------------------------------------------------------------
                  It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------

"Ken Wright" <ken.wright@NOSPAMntlworld.com> wrote in message
news:uXo0IxICFHA.868@TK2MSFTNGP10.phx.gbl...
> You can always create another Pivot table within the same workbook, but
not
> link it to the existing one,  You will pay the price in terms of filesize,
> but it will fix your problem.
>
> -- 
> Regards
>            Ken.......................    Microsoft MVP - Excel
>               Sys Spec - Win XP Pro /  XL 97/00/02/03
>
> --------------------------------------------------------------------------
--
>                   It's easier to beg forgiveness than ask permission :-)
> --------------------------------------------------------------------------
--
>
> "Neil Thompson" <neilthompson@eircom.net> wrote in message
> news:MxOLd.46730$Z14.30316@news.indigo.ie...
> > I'm bringing in data from an external database & creating a Pivot table
> with
> > grouped dates (Eg months)
> >
> > when this table is copied and the date grouping changed from say months
to
> > qtrs it changes the original to qtrs as well
> >
> > is it possible to avoid this happening without creating seperate
workbooks
> >
> > Many thanks
> >
> > Neil Thompson
> >
> >
> >
> >
> >
> >
>
>


0
ken.wright (2489)
2/1/2005 8:49:12 PM
Reply:

Similar Artilces:

Pivot Table Question #12
I've created a pivot table and it works well - counts the number of items in each category. What it doesn't do is count the number of blank cells. It gives me a blank category but, alas, it is blank. I want the blank category line to tell me how many blank cells I have. I know that this is possible, but just can't seem to make it work. Can someone help? Thanks! To count the blanks you could use a space character, or the formula ="", in the source data, instead of leaving the cells blank. The pivot table will be able to count those. Betty Csehi wrote: > I&#...

Automatically insert date and time in a side note
I know I can insert the current date and time in a note or side not by using sft+alt+f but would like to automate this so that the current date and time are automatically inserted into a side note when I create one by pressing the windows key + n. ...

Modify scale in X axis on a scatter plot from Pivot data
I need to increase the gap between my labels on the X axis in a scatter plot. They are too close and I can not read the labels unless i make them 4pt and zoom in to 200%. There are about 200 points on the X axis at .2 increments. I would like to be able to have labels for whole numbers only. I can not use a fixed set of values by generating a dummy X-axis since the range changes as I apply the different filters. Any ideas? thanks! Sandy Pivot charts don't let you change the spacing of the categories. They also don't let you make an XY (scatter) chart, so you probably have ...

Date in Headers and Footers
In Excel Office XP, how can I change the default date in a header and footer? The "&[Date]" command defaults to mm/dd/yyyy. I would like something else. Hi AFAIK this would require VBA. Would this be a feasible way for you? >-----Original Message----- >In Excel Office XP, how can I change the default date in >a header and footer? The "&[Date]" command defaults to >mm/dd/yyyy. I would like something else. >. > Thanks for your help, Frank, but since it take the Virtual Basketball Association ( :) ), I'll pass. If Microsoft isn...

assigning a null value of a date field to a date variable
Hi, At some point in the execution of my program I open a dialog box were the user is supposed to enter some info. Among other things he is asked to enter a date in a textbox, but this is optional. When I try to assign this date to a date variable I get the message “Invalid use of null” in the case the user has not filled in any date. If I use the Nz function, a value of 0 is assigned to the date and this later shows as December 30 1899 which I don’t want. How can I deal with this situation? Thanks in advance, George You cannot assign the Null value to a VBA variable of type Date. ...

Finding Certain Dates
I get paid on the 1st and 15th of every month unless that day falls on a weekend or holiday. If that happens I get paid on the business day prior to the 1st or 15th. I have a flash that pops up on paydays by nesting the IF function: (=(IF(AND(B200=2,C200=15),"P * A * Y * D * A * Y",IF(AND(B201=3, C201=1),"P * A * Y * D * A * Y",IF(AND(B202=3,C202=15),"P * A * Y * D * A * Y",IF(AND(B203=3,C202=30),"P * A * Y * D * A * Y", IF(AND(B204=4,C204=13),"P * A * Y * D * A * Y",IF(AND(B205=5,C205=1) ,...

Month to Date information
I am using a table that has the Date as the Primary key. I have built a userform that shows the various related Revenues that occured on that date, but I would like to show Month-to-Date information on that as well, i.e., if the current date being viewed is 20 July 2007, I would like the month-to-date add up all of the revenues from 01 July through 20 July. If I'm viewing 31 Aug 2007, I would like the revenues to add up 01-31 August. I am very new to Access,(so new, in fact, I have the "Dummy Book"), so dumb it down if you can. Thanks so much! This will give you all b...

Pivot Table Data Source, point to cell?
Is there any way to point the data source block to a cell. I want to simply update a cell (i.e. A1) with the range instead having to open the pivot table and changing from the wizard. Is there a way to do this? Example: Cell A1 contains: '[Output 3.24.xls]FORMALS-OUT'!$A$5:$P$65536 Data source in pivot table points to cell A1 to get the range. please tell me there's a way to do this.... thanx.! hi, Jeremy ! > Is there any way to point the data source block to a cell. > I want to simply update a cell (i.e. A1) with the range instead having to open the pivot table >...

linking Excel table to word
Can anyone tell me how I can have an excel table linked to a word table so that the word table dynamically updates itself based on the values in excel cells? Much appreciate it if anyone can point me to direction Thrava Hi in MS Word just insert the Excel sheet as Excel object (Edit - Paste Special) -- Regards Frank Kabel Frankfurt, Germany "Thrava" <anonymous@discussions.microsoft.com> schrieb im Newsbeitrag news:0cea01c46e91$faae80b0$a501280a@phx.gbl... > Can anyone tell me how I can have an excel table linked to > a word table so that the word table dynamically u...

Date time picker control's format in resource editor
This problem relates to the resource editor in Visual Studio .NET 2002/2003 If you create a date time picker control in resource editor and set its format to “time”, save and close the resource editor, when you open the resource editor next time, the format of that date time picker control will always be reset to “short date”. In a word, it cannot keep the “time” format. Antbody know how to get around it Thanks! >This problem relates to the resource editor in Visual Studio .NET 2002/2003: > >If you create a date time picker control in resource editor and set its format to �time�, ...

Generating a table of figures and table of tables using 'insert caption'
Version: 2008 Operating System: Mac OS X 10.4 (Tiger) Processor: Intel Hi, <br><br>I have set up a template in Word 2008 for some scientists to use. They want to insert captions using the 'Insert', 'Caption...' option for tables and figures, rather than using the style sheet 'figure caption' and 'table caption' that I set up so that the captions can be picked up by the cross-reference function. <br><br>Is there a way for my figure caption and table caption styles to be picked up by the cross-reference function? <br><br>Al...

Date Fields 01-22-08
Thanks to all the advice I have received but I seriously need more help (in more ways than one). The crux of the problem is that I need to insert a date into ClockStarts field and have access display another date into TargetDate field (plus 20 days but excluding Sat/Sun and holidays) If someone could give me the code to do this I would be forever in your debt. Thanks =Now() (if you want a date AND time value) =Date() (if you want date only) -- Regards Jeff Boyce www.InformationFutures.net Microsoft Office/Access MVP http://mvp.support.microsoft.com/ Microsoft IT Academy Program Ment...

Enlarged Input Box and today's date input
I have a sheet that I want a salesperson to record daily notes with a client. When they click on the cell I want a flyout type box (or enlarged box) that can scroll up and down to see past notes. I would like the lines to be entered bottom up so the last entry will be the one that shows in the cell when the cell is not selected. I would like the input line to automatically have today's date in the beginning and the cursor be next to that so an entry can be typed in. I hope this isn't too big a request! Thanks! Steve You may want to look at Data|Form to see if that does what...

formulae for dates
below formulae =TODAY()-(LEFT(C1,2)*365.25)-((MID(C1,7+FIND("",MID(C1,7,99)),2))*365.25/12) gives an output is mm/dd/yy on entering 0 years x months but if I enter 0 years 10 months or 11 months it gives me wrong date e.g if entered 0 years 10 months it gives 31 april 2006 plz advise thanks Hi Gerald You are missing the space between the quotes in your formula Try =TODAY()-(LEFT(C1,2)*365.25)-((MID(C1,7+FIND(" ",MID(C1,7,99)),2))*365.25/12) -- Regards Roger Govier "Gerald" <Gerald@discussions.microsoft.com> wrote in message news:8CF4029E-2BAA...

Median in Excel 2007 pivot-tables
HI, I am working on pivot table for data analysis, where I need to show the median. While working on this I found that "Median" is not supported in pivot tables. Is there any way I can use median function in pivot tables? Best Regards, Venkataramana AFAIK, you can't alter pivot tables, but you can certainly copy/paste special (perhaps a new sheet), and then do whatever you want to it (perhaps in an adjacent column). -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "Venkataramana" wrote: ...

Payables Invoice Due Date
I have a bunch of payable invoices that have dues date in the far away future. instead of the user typing 2003, they have put 2030. The voucher has been posted, but becuase the date is in the future, it did not apply and it still shows up on my aged trial balance report even though we have already payed it. I have gone into the tables and changed the due date back to 2003, but this still did not fix the problem. Any ideas? Thanks! Darren, You're saying that you have invoices that are fully applied, so when you look at them in Vendor Inquiry they have an 'Origin' of H...

Excel VBA problem
I have 2 Excel tables, part of the first table looks as follows. I kee track of parts in production by entering completion dates for eac process they go through. S/N Kitting SMT 1st Assembly Inspection 10001 30-Jun 2-Jul 8-Jul 10002 24-Jun 27-Jun 10003 Note: This table needs to be flexible in terms of the number of S/N' as well the number of processes entered. For example, there could b 15 processes parts need to go through. In addition, there could be 1 identical parts in production. I need help writing a macro which will go through each line and take the latest comple...

Pivot Table help needed
HI, I tried to the example given in this site... http://www.contextures.com/xlPivot03.html For Show/Hide Items -- Visible property is not working. Visible Property is working if the data source is EXcel itself. If the data source is analysis Manager, Visible property is always true...Is there any work around. I have done the macro recording. That is giving a long code which can not be implemented. also I will not able to add a calculated member in the pivot table because the olap datasource is analaysis amanager.. Thanks in advance for any help regarding this. Thanks and regards. ...

clear old items in pivot
How can I clear old items from a pivot table. I am running excel 2000. When I bring in new data and refresh the table I am seeing the new and the old data. Ideas? Hi see: http://www.contextures.com/xlPivot04.html -- Regards Frank Kabel Frankfurt, Germany "kimz" <anonymous@discussions.microsoft.com> schrieb im Newsbeitrag news:9d2e01c486d4$603a78d0$a501280a@phx.gbl... > How can I clear old items from a pivot table. I am running > excel 2000. When I bring in new data and refresh the table > I am seeing the new and the old data. Ideas? > I tried that and it wo...

Save Restore Point to specific date
Is it possible permanently save the Restore Point to specific date (one day) as file? No it's not. Restore points are not complete backups, they depend on the integrity all newer restore points to work - thus they cannot be used alone. "anime" <anime@nospam.microsoft.news> wrote in message news:uq00jx09KHA.2248@TK2MSFTNGP05.phx.gbl... > Is it possible permanently save the Restore Point to specific date (one > day) as file? "Dave and Rosanna" <davexnett05@yahoo.com> wrote in message news:ey8Oeh29KHA.4652@TK2MSFTNGP06.phx....

Part protection of Excel 2007 tables
Hi, Although this is not a VBA code/Macro related question, I thought that the people who know the Excel object model so well would be best placed to handle this question. Here it is: Can I part protect an Excel 2007 (ie lock certain columns and leave other unlocked)? eg. I want the data input columns to be unlocked and columns with formulas to be locked. When I did this and then protected the sheet, the table no longer behaved like a table. Was I missing something or is this a limitation of the Tables? Regards, Raj ...

Copy whole table into one cell
Office Excel 2003 on Windows XP I'm trying to create a report-maker with excel... ugh! Anyways, there is a template that users are filling out saying whether something is defected or not... that's not important, the important part is, that I'm having VBA go though the table (using a button after it is filled out) and deleting the rows that aren't defected... in other words, if there are blank cells in column C (for example), the whole row in which that blank cell is located is deleted. I'll never have more than 40 rows and 5 columns in the table so I don'...

table
with a vba macro I populated a table, the size of this table is variable in each execute this macro. and with this table I create pivot table, Database functions, but How to use all resources with all data? suggestions and examples thanks Marina Hi Marina Create a dynamic named range for your data. Insert>Name>Define>Mydata Refers to =OFFSET($A$1,0,0,COUNTA(A:A),10) The above example would create a range which contained as many rows as are present in column A, and the would be 10 columns wide. Change the formula as appropriate to your data location. Then in the Pivot Tab...

How can I turn off the automatic ordering of the data in Pivot Cha
I have some data, which I would like to rearrange in the chart, but it's automatically re-ordering it. I have tried manually re-ordering the data, but that's not helping. The Pivot chart seems to automatically override what order I put the data in, and charting it as it wants. I have tried unchecking the Automatic Formatting, but no change. Help? ...

Query Date Range
How can I use Between/And to prompt for date range that includes the start and end date? Hi, Design a select query for your table. add all fields to the design grid. In criteria box of the date field write this Between #BeginningDate# And #EndingDate# and run the query to find results. JB "karenfocus" wrote: > How can I use Between/And to prompt for date range that includes the start > and end date? My current criteria is in a query date field and is written Between [Enter start date] And [Enter end date] but when I run the query from the first day of the month to the...