Pivot.sourcetable property incorrect?

    I went to create a new pivot table in my workbook and a message came up
advising me to use the same source as my existing table.  I thought I *had*
used the same source so before replying 'Yes', I thought I better check the
source of my existing pivot table.  The only way I could find to do that was
VBA "msgbox activesheet.pivottables(1).sourcedata".  To my surprise this
showed as "ReportData!C1:C17" which I *know* is *wrong*.  It should be
something like 931 rows by 16 cols!

    Can anyone throw any light on this ie

- Why would the sourcedata property not be reported correctly?
- Can I check the sourcedata property without using VBA?
- Tell me anything else that might help unravel the mystery.

    I am now worried about the integrity of my original pivot table
(although it *looks* OK) and unsure whether to allow Excel to use the same
data source for my second pivot table.

    Thanks in advance for any advice.

    Marc


0
8/27/2003 11:34:23 AM
excel 39879 articles. 2 followers. Follow

1 Replies
277 Views

Similar Articles

[PageSpeed] 18

Sorry Sorry Sorry

I just realised that C1:C17 means col 1 to col col 17.
I thought it meant cell C1 to C17!

Sorry Sorry Sorry

Marc

"mab042" <notsupplied@notsupplied.com> wrote in message
news:e$yft7IbDHA.2372@TK2MSFTNGP10.phx.gbl...
>     I went to create a new pivot table in my workbook and a message came
up
> advising me to use the same source as my existing table.  I thought I
*had*
> used the same source so before replying 'Yes', I thought I better check
the
> source of my existing pivot table.  The only way I could find to do that
was
> VBA "msgbox activesheet.pivottables(1).sourcedata".  To my surprise this
> showed as "ReportData!C1:C17" which I *know* is *wrong*.  It should be
> something like 931 rows by 16 cols!
>
>     Can anyone throw any light on this ie
>
> - Why would the sourcedata property not be reported correctly?
> - Can I check the sourcedata property without using VBA?
> - Tell me anything else that might help unravel the mystery.
>
>     I am now worried about the integrity of my original pivot table
> (although it *looks* OK) and unsure whether to allow Excel to use the same
> data source for my second pivot table.
>
>     Thanks in advance for any advice.
>
>     Marc
>
>


0
8/27/2003 11:41:44 AM
Reply:

Similar Artilces:

Set Form Property from a Different Form
In Access 2003 I have a form with a property defined as follows: Public wtPrelimFinal as string This form, and a couple of similar forms, call a dialog form where the user picks between two options. I want to pass the name of the form and the proprty to the called form as follows, for example: "Forms!ThisForm.wtPrelimFinal", using OpenArgs, then storing the string in a variable, say strForm in the called form. When the user selects an option, I want to reset the value of wtPrelimFinal in the calling form. Hard coded, it would be somthing like: Forms!ThisForm.wtPrelimFinal = ...

Set Account Owner with Form Properties OnLoad and OnSave
My primary goal is to have Account Owner (ownerid) set automatically based on the zip code of the account. Currently, I am attempting to do this via the Form Properties OnLoad() and OnSave() events. Here is an example which does not work: else if ((addy2 == "46142") || (addy2 == "46143") || (addy2 == "46158") || (addy2 == "46217") || (addy2 == "46227") || (addy2 == "46184")) { crmForm.all.new_branchtxt.DataValue = "Greenwood"; crmForm.all.ownerid.DataValue = "66AA42F3-6781-4829-9370- E01BC996688C"; } You also n...

Property page #2
This is a Windows CE.net question but I find that posting here gives greater exposure and feedback - Windows CE.net does not support the dialog wizard functions. Therefore, I would like to create one myself. Is there a way to keep property pages from displaying their tabs? Thanks, ...

Quickie on Pivots
Is it possible to force a pivot table to display the column information. Instead if this format Category 1 Apples Pears Grapes Category 2 Nuts Raisens I want to display this instead Category 1 Apples Category 1 Pears Category 1 Grapes Category 2 Nuts Category 2 Raisens Thanks It's easy enough to add the data after it has been generated by the Pivot table, but that usually involves breaking the Pivot table links though. Do you need it to remain dynamic? -- Regards Ke...

Pivot table-date grouping
I receive excel data from six outside agencies on a monthly schedule. One of the columns is Date. When I put all the data together on one ss, I can create a pivot table but I cannot group the dates. (I know it is a date format problem as I created a dummy ss from scratch and was able to create the chart.) Each date cell has a green tic in the top left corner. Format|Cells tells me it's a date but the infobox next to each date cell shows me a list of options. How can I take the existing column of dates and maybe move them to a new column with a correct date format so that I can use t...

Pivot Table Question
Hi All I originally posted this in microsoft.pulic.excel but was advised to post here as well - so please don't yell at me about posting to multiple groups :) *** is there a way in a pivot table to only use (& therefore group on ) part of a value name ... e.g. if i have Car Number BMW - Blue 100 BMW - Red 20 VOLVO - Blue 50 VOLVO - Red 10 and i want a pivot table to look like this Blue Red Total BMW 100 20 120 VOLVO 50 10 60 150 30 ...

saved publisher file opened with incorrect page order
I created book pages with two-page spread master pages and separate master page for first page. I flowed text, added objects and pictures. I saved often over 3 days. I now have 38 pages completed. When I opened the document today, the pages were out of order. (page 13 appears as page one (single page); other pages are not all in order. I have tried: inserting new blank pages, moving pages, etc. but am stymied. I have not resaved the document today so that I am left with document I first opened today. Any suggestions would be helpful. -- Ndlc Have you tried re-setting the public...

consolidating data for a report - HELP
Hi The spreadsheet shows people on up to 20 differant projects with each project have 2 colums of data i.e. BY and BZ. My example cuts it down to 4 columns and 7 rows as below i.e. A | B | BY | BZ Project Name Fred Rate 400 B 1 400 S 0.5 200 X ...

SBS 2008 Notifications Incorrectly Reporting Disk Space
I'm having a strange problem with an SBS 2008 server. After clearing up a disk space issue on the C drive I configured notifications to email me if the space goes below 10% free. Currently there is 9 GB free on a 60 GB partition. However, I keep getting email alerts saying the free space is below the threshold - Volume Name: C: Free Space: 6353383424 Volume Size: 79933267968 Why is it reporting the volume size and free space incorrectly (80 GB and 6 GB free rather than 60 GB and 9 GB free) ? I can't find anything on Google relating to this Any help greatly appre...

Auto-refresh pivot table?
Hi. Is there a way to set up a pivot table so it will automatically update when its source data changes. It seems that by default, I need to explicitly tell it to refresh. Thanks! Ken kk_oop@yahoo.com wrote: > Hi. Is there a way to set up a pivot table so it will automatically > update when its source data changes. It seems that by default, I need > to explicitly tell it to refresh. > > Thanks! > > Ken I'll answer my own question. Enter the following VBA code for the worksheet that contains the pivot table (get to the code by entering alt+F11): Private Sub W...

Data source reference is not valid in pivot table wizard
Hi, I'm trying to create a pivot table in Excel 2007, something I have done a thousand times in 2003. However, no matter what I do, the wizard just tells me that the data source reference is not valid. I have a table with ITEM NAME, COMPANY NAME, QNT, PRICE, LINE PRICE etc as columns. Each column contains only one data type (ie either a number, or a currency, or a date etc) and the whole table is set as a table with the name SALES. Whether I type the name of the table, or let the wizard select it, or type a range of cells, or drag using the selector in the wizard, i just says the same t...

Properties of contacts
Hi to everyone!!! I need your help! Does anybody know how to add or if it is possible to add properties of contacts in Exchange? Thanks for your helping! Gorka Rodriguez on Exchange 2003 just double click the contact and type all the properties you want. Maybe you mean adding extra properties, so you need to go Detail templates, contacts,double click contacts and then go to templates tab and ADD or MODIFY all the properties you need to. >-----Original Message----- >Hi to everyone!!! > >I need your help! >Does anybody know how to add or if it is possible to add propertie...

AD Properties and Outlook Views.
Hi, We have an issue whereby all our user details are hidden in Exchange using the user template in Exchange System Manager. This works fine for hiding private information when viewing user properties in Outlook however if someone right clicks on the user and selects "add to contacts" all this information is pulled into the contact form. Does anyone know where this can be locked down or do we need to secure the actual attributes in AD? Thanks for any help in advance. Cheers Leon Hi Leon, I have the exact same problem here. Did you find a solution ? "LeonG" wrote: >...

Code to add gridlines to Excel Pivot Tables
This was more cumbersome than I realized (at least with Excel 2003). I found this code on another forum and it works like a charm: Here's the link to the post >> http://lounge.windowssecrets.com/index.php?showtopic=737024 Here's the post itself: You can create code that will format the pivot table automatically each time it is updated: - Right-click the sheet tab of the worksheet that contains the pivot table. - Copy/paste the following code into the worksheet module: Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable) With Target.TableRange1....

Word 2007 crasshes when selecting advanced properties
When I click the office button, prepare, properties and then select advanced properties from the document properties drop down, Word closes immediately. No error message is displayed and nothing shows up in the event viewer. This same behavior occurs in Excel 2007. I'm running XP SP3 and Office 2007 SP2. Any help would be appreciated. Have you run the Office Repair option? It may also be another symptom of a corrupt Word Data key (see below): -- That may be a symptom of a Word Data Key corruption - usually because Word has been running when updates are installed. ...

Pivot table warning dialog suppression
[Excel 2003] Is there a way to suppress the warning dialog which appears asking if you want to replace the cells when you refresh a pivot table? Can anyone help? ...

Pivot Tables Sort
I am having trouble trying to sort the 13 cycles in a pivot table. When I try to sort it comes up like this 1 1 1 1 I want a normal numeric sort. 1,2,3 ... I have tried to sort by values, but it gives me the "sort refernece is not valid" error. I have also tried to alter the sort in options, but them I get something even screwier than I started. I have also tried to change the formatting of the cells. Does anyone have any clue what's going wrong? Thank I've tried to work out what's been going wrong, but I don't get the sam errors. Are you trying ...

Pivot Tables
[Excel 2003] I have a question regarding the activation of pivot table refreshes from a userform. I tried to use the following code in a command button without success: ActiveSheet.PivotTables("PovotTableName").RefreshTable I think the code doesnt work because the userform is in another sheet to the pivot table, however I need this to be the case since I have 10 different sheets, each with a different pivot table. Can anyone help ? Roger, Always worth looking in the VBA helpfile which gives good direction on both questions. Try following & see i...

How to pivot this
Need help... how to pivot the following:- counter type AMOUNT ------- ---- ------- 1 A 10 1 A 10 2 A 20 3 A 30 3 A 30 4 A 40 5 A 50 5 A 50 1 B 10 2 B 20 3 B 30 3 B 30 3 B 30 4 B 40 5 B 50 5 B 50 5 B 50 result:- A B TOTAL - - - 1 $$ $$ $$ 2 $$ $$ $$ 3 $$ $$ $$ 4 $$ $$ $$ 5 $$ $$ $$ Thank you. Would this help: Create table x(kounter int,TypeA char(1), Amount int) GO INSERT x(kounter,TypeA,Amount) VALUES (1,'A',10) INSERT x(kounter,TypeA,Amount) VALUES (2,'A',20) INSERT x(kounter,TypeA,Amount) VALU...

Hyperlinks displayed incorrectly when composing
Hello :-) I am using Microsoft Outlook 2003 and send/reply to e-mails in HTML, using Word as my editor. I am having a problem whenever I compose, reply-to or forward an e-mail. Once the compose window opens up, whenever you create links in the body of the message (or if any are present already), they are not displayed in the usual way of being underlined like they would in MS Word. Instead they format as: { HYPERLINK "http://www.example.com/" } However when they are sent, they appear to the recipient in the usual way or being blue and underlined so you can click them. Is there a...

PIVOT TABLE #48
SHOULD ANY ONE KNOW SOME ADVANCE STAGE OF PIVOT TABLE. I KNOW ALL BASIC OF PIVOT , BUT IN SEARCH OF SOME THING EXTRAORDINERY. THANKS IN ADVANCE SANJEEV Not sure how "extraordinary" you want to go <g>, but think it'll be worth trying Debra's pages on Pivot Tables as a start. Browse the many links under "P" at her index page: http://www.contextures.com/tiptech.html (also, look at GetPivotData under "G-M") ... btw, please do not post in all caps. It's tough to read, and may be considered impolite by many .. -- Rgds Max xl 97 --- Singapore, GM...

More Pivot Table Grouping problem
I have read the answer to the Pivot Table Grouping issue sent in by Hrider about a year ago. I have the same problem but the solution doesn't seem to apply. Still when I try to group the datesI get an error "Cannot group this selection". I have a two column list with column one being calendar dates sorted oldest to newest. The second column is values associated with each date. I have made certain that the dates are not text but actual date values by selecting the column and Formatting cells as Number/Date/Jan-01-2001. I think that should make sure the values are dates. Wh...

Public Folder path display incorrectly in GAL
I have E2K3 with SP1 running on W2K3. I have created some new mail enabled Public folders. The path in the GAL would not appear or only the Alias name would appear. I want the full path to be displayed so that the folder can be located in PF hierarchy. I do not believe this is possible unless you included the path as part of the alias. Of course that would not make any sense. Why do your users need to know the path? Nue "Mukks" <Mukks@discussions.microsoft.com> wrote in message news:B38AF6F1-6C00-4075-B173-C8288F716528@microsoft.com... >I have E2K3 with SP1 running o...

Pivot table in Excel 2007
Hi, Somehow my Pivot table do not sums up by rows. Boxes are checked for both columns and rows for Grand total under options, Tools and Filter. Dinesh You'll only see row totals if you have fields in the Columns area of the pivot table. The row totals would be a sum of those values. If you have multiple fields in the Values area, they won't be summed in the row totals. Dinesh wrote: > Hi, > > Somehow my Pivot table do not sums up by rows. > Boxes are checked for both columns and rows for Grand total under options, > Tools and Filter. ...

My pivot table field list show duplicates
I have a pivot table that uses a custom sort order for a row field. It groups according to 0, 1-4,5-10, etc. When I click on the drop-down arrows, I see the field data in duplicates or even triplicates. ...