Pivot Table - Column Over Written There Must be a way to

[Arts]

Hi all

I had created a series of pivot tables and I had no idea the row section could be edited i/e over written. I can't seem to find a solution for the life of me to reset the data to it's original value, example below using simple data

we have the below pivot table

But someone had overwritten one of the row labels which I didn't think was possible, James Lobeon is now 568. I assumed I would get the message "We Can't change this part of the PviotTable"

But I cant seem to get it back to it's original value. I have tried refresh, refresh all and the value doesn't seem to change to its original state. I did find this quite strange as I assumed this would go back to the original source data once refreshed and remove the 568 as that value isn't in the original source data. I did find a way to clear the whole pivot table by going to "PivotTable Analyse", Clear> Clear All but this deletes all the information from the pivot table meaning you would have to rebuild it from scratch and if a large pivot table which in my case it is having to do this each time would be quite annoying.



As always any help on this is most appreciated.

Arts

 

[Alex Blakenburg]

If anyone has a non macro way of fixing should this issue occur again that would be most helpful/appreciated!

In terms of a non-macro way, I just can't replicate the issue you are having with the shared workbook.
Since the macro works that seems to eliminate that the possibility that it is a different issue.

I tried using 2 pivot's sharing the same Pivot Cache and also adding a calculated field, and the previous manual method still works.
I tried using a data model based Pivot and it worked.

I assume you tried it on the server copy a few time to make sure you did a Pivot Refresh after removing the offending field from the pivot row area in the pivot and before putting it back.

You said your test file was a made up one. What happens if you download the server file and do the manual fix steps on the downloaded copy ?
If you can manage to desensitize the data without the issue disappearing and want to share the file via google drive, dropbox etc, I am happy to take a look.

 

[Arts]

Hi Alex

I've just picked this up its approaching 11 pm here. I'll see if I can get a copy minus the important company information some time tomorrow but if there is no manual fix its not a huge deal anymore.

The method that was provided earlier by Steve by locking the sheet and allowing them to edit objects should stop it happening from going forwards but I'll see if I can get a copy of the file.

Once again appreciate all your help with this.