Results 1 to 6 of 6
  1. #1
    Star Lounger
    Join Date
    Sep 2003
    Location
    Houston, Texas, USA
    Posts
    87
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Corrupt Pivot tables (Excel 2000)

    We have a large file with multiple pivot tables. When the user changes the source data - deletes rows etc, the pivot tables cannot be refreshed. When the user first starts out with a spreadsheet he has no problems with the pivot tables. After about 3 of them, he starts adding or deleting data and the pivots corrupt. I told him he needed togo to Access, but would like to give him an Excel answer also. one of the things I suggested was to use Excel data on the disk and create the pivot(s) in a new file.
    Does anybody have another suggestion?

    Thank you so much for any and all help.

    deutsch

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Corrupt Pivot tables (Excel 2000)

    What kind of problems are the users seeing? I would not think there would be an issue with deleting rows in a pivot causing refresh issues or even corrupts them. I can see deleting columns causing a problem but rows should not be an issue.

    How are your table ranges defined to ensure that the pivot table source remain the entire datarange whether you add or subtract data?

    Steve

  3. #3
    Star Lounger
    Join Date
    Sep 2003
    Location
    Houston, Texas, USA
    Posts
    87
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Corrupt Pivot tables (Excel 2000)

    Basically, we cannot refresh after deleting rows. The user is trying to filter data and then use the pivot to show the filtered summarized data. I do not think he is deleting columns.

    Thanks,

    deutsch

  4. #4
    Star Lounger
    Join Date
    Sep 2003
    Location
    Houston, Texas, USA
    Posts
    87
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Corrupt Pivot tables (Excel 2000)

    What I have discovered in the meantime is the following:
    When we delete the rows, the Pivot table updates.
    When we cut and then paste the rows to another sheet, the Pivot table will not update. The error that comes up is 'The command requires at least two rows of source data.'
    It seems that the Pivot table has lost its source range information. When we re-set the range, all works well.
    This is awkward. Is it normal for Excel 2000 and 2002?
    Any way around this?

    Thanks,

    deutsch

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Corrupt Pivot tables (Excel 2000)

    By cutting rows from the source data and pasting them into another worksheet, you are effectively breaking the source data into multiple pieces. This confuses Excel. Instead of cutting and pasting, try copying and pasting to transfer the data to another worksheet, then delete the rows. That way, no confusion will arise.

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Corrupt Pivot tables (Excel 2000)

    In addition to Hans comments,
    Perhaps you should create a dynamic range name as the source for the pivot using the offset function. This could expand and contract as data is added and removed and should be unaffected by the moves.

    For example if your source is:
    Sheet1!A1:G100

    adding more, moving cells and inserting will change what should be the source.
    If you create a named range (insert name define)
    Name: PivotRows
    <pre>=COUNTA(Sheet1!$A:$A)</pre>


    Name:PivotData
    <pre>=OFFSET(Sheet1!$A$1,0,0,pivotrows,7)</pre>


    This range will expand and contract. (I assume that column A will not have any blanks within the dataset).

    Use the range "PivotData" instead of "Sheet1!A1:G100" as your pivot source and it should always be valid.

    (of course modify the example for your sheet names and number of columns)
    Steve

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •