Results 1 to 9 of 9
  1. #1
    Star Lounger
    Join Date
    Nov 2001
    Location
    Sydney, Australia, Australia
    Posts
    75
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Pivot table wrong value (2000 (and 2002))

    Can anyone tell me why I'm getting the row value of 'July 2005' in this pivot table (on the incomesummary sheet)? As far as I can see, it doesn't appear anywhere in the data.

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

    Re: Pivot table wrong value (2000 (and 2002))

    My guess is that Excel has become confused. I'd scrap the pivot table and create it anew.

  3. #3
    Star Lounger
    Join Date
    Nov 2003
    Location
    Germany
    Posts
    75
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pivot table wrong value (2000 (and 2002))

    <<Can anyone tell me why I'm getting the row value of 'July 2005' in this pivot table>>

    Because you typed it in there.
    Overwrite it again with January, and it will be as you expect it.

    This is in my opinion one of the "dangerous" features of Pivot tables, because once you have changed a field name (in the pivot table) it is not so easy to bring it back to the original (I guess only be possible by VBA)


    Best regards

    Wolf

  4. #4
    Star Lounger
    Join Date
    Nov 2001
    Location
    Sydney, Australia, Australia
    Posts
    75
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pivot table wrong value (2000 (and 2002))

    The problem is not in the field name (which is 'month'), but in the field value. Overtyping the value with 'January' doesn't work (Excel doesn't permit changes to the cell). I'll try scrapping and re-creating the table as suggested. (This certainly doesn't reassure me about using Excel for accounting stuff)

  5. #5
    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: Pivot table wrong value (2000 (and 2002))

    You can make changes, though you can not make it into a name that already exists. It is best to recreate that field if not the entire table.

    It is not a good practice to edit the items in the the fields since (as was mentioned) they tend to stay in the list and are not cleared out.

    Steve

  6. #6
    Star Lounger
    Join Date
    Nov 2001
    Location
    Sydney, Australia, Australia
    Posts
    75
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pivot table wrong value (2000 (and 2002))

    Thanks for all the suggestions. In the end I just deleted the pivot table and re-created it (fixing the problem). I'll look out for this in future.
    Thanks again.

  7. #7
    Star Lounger
    Join Date
    Nov 2003
    Location
    Germany
    Posts
    75
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pivot table wrong value (2000 (and 2002))

    [edited 12:15]

    Hi Bruce,
    sorry , I meant the field item.

    Actually, I was able to change the July 2005 to January, but I ran a macro before (what I usually do with new used Pivot tables).
    This macro is deleting all redundant field values. In your case it was deleting the January (and all other not used months) in the background, therefore January could be typed in again. (see also Steve's remark).
    I am doing this "cleaning" in some of my projects automatically when a workbook is onpened,

    IMHO Excel Pivot tables are very powerful tools for all kind of statistics. You only have to make sure, that changes can only be made in the database, not in the Pivot table directly, e.g. by protecting the sheet.

    Here is the macro:
    (I suppose that you can handle macros, if not, let us know)
    Deleting the redundant items solves some problems, but - depending on the structure of your pivot table - it might have some negative influence (If you are using redundant items intentionally). Therefore please test it before in a copy of your original workbook. Depending on how many items you have, it may take quite long.

    Put your cursor in the Pivot table in the field "Month" and run the following macro:

    Sub DeleteRedundantPivotItems()
    Dim PI As PivotItem
    On Error Resume Next
    With ActiveCell.PivotField
    For Each PI In .PivotItems
    PI.Delete
    Next
    End With
    End Sub


    Best regards

    Wolf

  8. #8
    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: Pivot table wrong value (2000 (and 2002))

    I would also be "leery" of clearing the old items and then typing in the correct value. I think this will cause the same type of problem in the future.

    By manualling changing it, the pivot no longer updates it changes in the data. You need to clear and reset it, so it is again reading them automatically.

    [The best thing is not to edit them in the first place <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

    Steve

  9. #9
    Star Lounger
    Join Date
    Nov 2003
    Location
    Germany
    Posts
    75
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pivot table wrong value (2000 (and 2002))

    Steve,
    <<The best thing is not to edit them in the first place>>
    I completely agree

    <<By manualling changing it, the pivot no longer updates it changes in the data. You need to clear and reset it, so it is again reading them automatically.>>

    Just for fun:
    Use Bruce' example. Refresh the PT, run the macro in the field month, change July 2005 to January.
    Then add in the database some more lines for January and other months, and it will work properly, as there was never something else appearing.
    I realized that some time ago, also to my surprise.

    Best regards

    Wolf

Posting Permissions

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