https://bugs.documentfoundation.org/show_bug.cgi?id=162417
--- Comment #3 from Robert Lacroix <[email protected]> --- 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 the assignee for the bug.
