Pivottable feature(?) in Excel 2003 SP-1

[Guest]

In all earlier versions of Excel, pivottable page, row, & column elements could
not be changed...now, in 2003, we find that the text of an item can be over
typed,
retained, and not returned to it's original form during a refresh. This seems
a little odd.

Is this a new 'feature' or just a new bug?

 

[Guest]

Either within the Field Options dialog or by typeing directly over the Field
Name in a cell, there has been at least one way to change the Field Names
permanently at least as far back as Excel 2000.

tj

 

[Debra Dalgleish]

The behaviour hasn't changed -- in Excel 97 and Excel 2002 I can type
over the items in row or column headings, and type over the names in
field buttons. These remain changed when the pivot table is refreshed.

 

[Guest]

Learn something new every day...I have often changed the Field names of
row/column items in pivot tables but don't recall (maybe I never tried) being
able to overtype an actually row/column line item value. Now that I've seen
it, I don't
care for it much.

Know of any way to prevent it while still allowing changes/refreshing of the
pivot table?

 

[Debra Dalgleish]

You can protect the worksheet, and allow users to use the PivotTables:

Choose Tools>Protection>Protect Sheet
Add a check mark to 'Use PivotTable Reports'
You can use a password
Click OK

 

[Guest]

Pivot Tables in my workbooks cannot be refreshed when I turn on protection. I
have checked that the cells are unlocked, and cannot get a worksheet to be
protected and allow a refresh.

Any thoughts?

 

[Debra Dalgleish]

You could record code as you unprotect the sheet, refresh the pivot
table, and reprotect the sheet.

Then, run that code as required, e.g. after you've updated the source
data, or when the pivot sheet is activated.

 

[Guest]

Debra Dalgleish, if I understand correctly, suggests recording a small macro
to allow refreshing a protected pivot table.

But what is the point of the 'Tools, protect sheet, Use pivot table
reports' ? It seem to allow nothing.

How is it supposed to work or is my Excel faulty?

 

[Debra Dalgleish]

Allowing "use pivot table reports" means that users can change make some
changes to an existing pivot table, such as moving fields, showing and
hiding items, and sorting.

To use other features, such as refreshing the pivot table, the sheet
must be unprotected.

 

[Guest]

Thank you Debra Not the worst disappointment with Excel XP/2000 [The whole
Pivot table stuff now seems to have been 'improved worse' in a big way eg
annual total including bits from preceding and following years, and
'getpivotdata' instead of a regular cel ref .....]

 

[Debra Dalgleish]

You're welcome. I'm not sure what you mean about the annual totals, but
uou can prevent the GetPivotData formulas from appearing. There are
instructions here for adding the Generate GetPivotData button to a
toolbar, and toggling the feature on and off:

http://www.contextures.com/xlPivot06.html

Thank you Debra Not the worst disappointment with Excel XP/2000 [The whole
Pivot table stuff now seems to have been 'improved worse' in a big way eg
annual total including bits from preceding and following years, and
'getpivotdata' instead of a regular cel ref .....]

:

Allowing "use pivot table reports" means that users can change make some
changes to an existing pivot table, such as moving fields, showing and
hiding items, and sorting.

To use other features, such as refreshing the pivot table, the sheet
must be unprotected.