Pivot table, can you update the source data directly from the pivot table view

[pennyarcade]

Hi, I want to know what is happening here:

- I have a pivot table view with multiple fields under the "Row" section.
- I want to update a certain date in the pivot table so that the data set is updated.
- All dates with the same original date within the pivot table get changed.
- Data set is not changed.

Can this be done?

If it isn't possible for the data set to be updated from the pivot table, can I prevent a user from changing a cell in the pivottable?

 

[Jerry Sullivan]

Hi and welcome to MrExcel,

When you directly edit the value of a cell with a PivotItem in the Row section of a PivotTable, it changes the Caption property of the PivotItem. That new caption will be displayed in all instances that the PivotItem appears. The PivotItem has a SourceData property that is not changed when the Caption is modified that way. And as you observed the values in the actual source data (range) do not change.

The Contextures site has some examples of how you can prevent editing of PivotTables using VBA.
http://www.contextures.com/xlPivot12.html

 

[pennyarcade]

Thanks Jerry,

Is there any way to clear the new caption once it has been set? I can't seem to revert it back to the value that is in the sourcedata.