+ Reply to Thread
Results 1 to 9 of 9

Pivot not refreshing - very bizarre

  1. #1
    Registered User
    Join Date
    08-08-2010
    Location
    Ottawa, Canada
    MS-Off Ver
    Excel 2010
    Posts
    85

    Pivot not refreshing - very bizarre

    Hi, I have a very bizarre problem. I have a file that is several years old. It houses data for training audits by new staff, and creates reports for the trainers via preset pivot tables (and other formuals/charts/macro to update the filters on the pivot tables). The pivots read a tab called Data, from the same named range (datasource A:AU).

    Every new year, we roll over the file for the next year. That means we renamed the file "ABC 2019", delete the 2018 data and the trainers start rolling up with the new 2019 data.

    However, this year, when the trainer did that with the first training audit, when the macro refreshed the pivot table, instead of picking up just the 1 new staff name (John Smith), it brought in PART of an staff name who hasn't been with the company for 3 years!?! That name has NOT been in data data to roll up for 3 years. In the pivot filter dropdown, just her first name and first 3 letters of her last name show up. (i.e. Jane Dai)

    I deleted the tracker data from the Data tab, then re-copy pasted it over. Same thing. Even those the data tab clearly has John Smith in the NAME field, it shows Jane Dai in the pivot drop down. I added a new staff audit underneath John Smith. The pivot then refreshed and now shows "Jane Dai and Dave Green"??? So then deleted all the data for John Smith, added a space at the end of his name from the trainer audit checklist (where the data comes from). And the Pivot updates with "John Smith ". So I tried to roll up the audit without the space... it shows again as "Jane Dai" in the pivot... why???!!

    This is driving me nuts. As I said, we've used this file for YEARS... how is it bringing in a name that hasn't appeared in any datasource for over 3 years? But seems to bring in other names fine... just brings in Jane Dai whenever I roll up John Smith?

    Has anyone ever seen something like this before?

  2. #2
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,373

    Re: Pivot not refreshing - very bizarre

    What do you see in Pivot Table > Options > Change Data Source?
    Entia non sunt multiplicanda sine necessitate

  3. #3
    Registered User
    Join Date
    08-08-2010
    Location
    Ottawa, Canada
    MS-Off Ver
    Excel 2010
    Posts
    85

    Re: Pivot not refreshing - very bizarre

    Hi, yes I went through that first to make sure it was pointing to the right cell ranges. And I even reset the range name, re-added it to the pivot table.

    It only seemed to happen with that one person's name. When I rolled up another new staff audit Dave Green, he's name appeared fine in the filter. I think the file is corrupted
    Last edited by trillium; 02-10-2019 at 08:56 PM.

  4. #4
    Forum Expert kersplash's Avatar
    Join Date
    11-22-2016
    Location
    Perth
    MS-Off Ver
    Home 2016 (Windows 10)/Work 2013 Pro Plus (Windows 10)
    Posts
    2,012

    Re: Pivot not refreshing - very bizarre

    Are you able to post enough of the data in an attachment with the error present....delete all irrelevant columns and keep a few names?

  5. #5
    Forum Expert kersplash's Avatar
    Join Date
    11-22-2016
    Location
    Perth
    MS-Off Ver
    Home 2016 (Windows 10)/Work 2013 Pro Plus (Windows 10)
    Posts
    2,012

    Re: Pivot not refreshing - very bizarre

    It could be a pivot table grouping that has been renamed "Jane Dai".

  6. #6
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,613

    Re: Pivot not refreshing - very bizarre

    Go to
    PIVOT TABLE OPTIONS>DATA Tab>Retain items Deleted from data source> if its set to automatic (then you've found your problem)....change it to NONE
    Press ok and refresh
    If you are satisfied with the solution(s) provided, please mark your thread as Solved.
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

  7. #7
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    UK
    MS-Off Ver
    2010 primarily
    Posts
    2,951

    Re: Pivot not refreshing - very bizarre

    It sounds like someone typed over the original name in the pivot table to me.
    Rory
    Days when we raged, we flew off the page
    Such damage was done

  8. #8
    Registered User
    Join Date
    08-08-2010
    Location
    Ottawa, Canada
    MS-Off Ver
    Excel 2010
    Posts
    85

    [SOLVED] Re: Pivot not refreshing - very bizarre

    Humdingaling - That seems to have worked! I didn't know about that area in Pivot Options before!! THANK YOU!

  9. #9
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,613

    Re: Pivot not refreshing - very bizarre

    glad to have helped

    i had found it when faced with similar problem to yours a few years

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1