[Bug 162417] PIVOTTABLE: Warning does not show range when overwriting destination range contents

[bugzilla-daemon at bugs.documentfoundation.org]

https://bugs.documentfoundation.org/show_bug.cgi?id=162417

--- Comment #3 from Robert Lacroix <muxlab at hotmail.com> ---
On second thought, there might be a different approach to solving the problem
than what I have requested. The enhancement I have suggested is "thinking
inside the box". Let's see what I really want, and let UI experts weigh in on
potential solutions.

The change as requested will allow a user to identify which pivot tables want
to expand when pivot tables are refreshed, without actually letting them grow
(overwrite == No).

Pivot tables may be bordered by "canary" cells when these tables are not
expected to grow. Such pivot tables typically have row fields or column fields
or page fields with a specific set of values selected, and the user wants only
these values to be selected.

When a new value (perhaps a typo) appears in a cell in the source range, this
causes a new value to be added to the list AND SELECTED in every pivot table
which includes the offending cell's column as a row field or column field or
page field. A new value for a row field grows a pivot table vertically, and a
new value for a column field grows a pivot table horizontally. Canary cells are
a way of identifying this growth.

Spotting pivot tables which have grown is easy in workbooks with one or two
pivot tables. It's not so easy when a workbook contains 55 pivot tables spread
across 45 sheets, hence the desire to show ranges that would be overwritten.

In general if each worksheet contains only one pivot table and no additional
computations nearby, then a user may allow canary cells to be overwritten
without creating havoc. The user then fixes the field selections in the altered
pivot tables and restores the canary cells after the pivot table has shrunk
back to size. The situation is more critical when there are nearby computations
or adjacent pivot tables on the same sheet that are wiped out by overwriting.

Giving a second thought about this use case, it would actually be more valuable
to allow a user to "lock" row fields and column fields (and page fields) so
that new values appearing in selection lists are not automatically selected.
Then dimensions of these pivot tables does not change. It would also be useful
to indicate somehow that a new value has appeared in the list for locked
fields, so that typos can be fixed in the source range or new values in the
list can be considered for inclusion among the blessed. This actually is my
preferred solution, because it strikes at the root of the problem, rather than
merely dealing with side effects. It also eliminates the need for canary cells,
and more importantly protects the contents sheets near these pivot tables.

-- 
You are receiving this mail because:
You are on the CC list for the bug.