https://bugs.documentfoundation.org/show_bug.cgi?id=166275
--- Comment #8 from Robert Lacroix <[email protected]> --- (In reply to Heiko Tietze from comment #7) > Perhaps we find a solution to the actual use case. I doubt that you need to > see every tab resp. to click on any. For example, if you feed the sheet from > external sources we could flash the tab or highlight it, if you refer in a > formula to sheet$foo this could become hyperlinky, etc. Since we are talking about use cases, the following should actually end up in a new UX bug request for Pivot Tables. Feel free to fork one from this conversation. What you see above is actually simplified my use case. My file also has "control" tabs which affect what calculations do in the data tabs. The several control sheets have unique formulas and layouts. The 71 data sheets have identical layout except for the number of rows containing actual data. Each data sheet has a set of fixed header rows which are identical across all data sheets, containing such things as a reference to a control sheet cell, and a list of valid values for data in that spreadsheet column to appear in the Pivot table. The Pivot Table data range extends into the header above the actual data to add the minimum required categories for that spreadsheet column. Locking the Pivot Table column field to a subset of these header values fixes the Pivot Table to a static set of columns regardless of what data appears in the data rows. It would be ideal for Pivot Tables to have optional "validation" sheet ranges in the layout dialog attached to column fields, which are separate from the data. Then I would not need these header rows in every data sheet - they could point to a control sheet. A validation range is an attribute of a column field in the Pivot Table so that it provides a list of canonical values which may appear in data of that spreadsheet column, even if the data range is empty. The goal is to have a set of static Pivot Table columns. Using a validation range for a column field would automatically provide an initial set of values to lock the categories of the field. I am working around this ideal structure by extending the Pivot Table up into the header rows so that validation values are included, and manually locking the desired category values so that valid values which are not used, or invalid values which may appear in the data, do not cause extra columns to appear in the Pivot Table. This was the motivation to ask for the "Lock" attribute of category fields, as well as protecting adjacent spreadsheet cells from being overwritten by the Pivot Table. -- You are receiving this mail because: You are the assignee for the bug.
