Description: Problem: The standard way has a user interacting with the Pivot Table Layout dialog box to extend the source data Selection to include new data below the existing range. This is unsatisfactory when data rows are added frequently, as there can be more fiddling with the dialog box than with the data being added. This problem is magnified when multiple pivot tables use the same source data range. Workaround: Use an "end-of-data" value as the last row of source data and insert rows before this value to automatically change the source data Selection range of all pivot tables using the same source data range. This workaround is good when source data does not include formulae, but more difficult when the source range includes computed cells containing relative references, because generally a table of values with a computed column should use the same relative formula in all the rows. It is more difficult because formulae referencing cells below the insertion point will differ from cells above the insertion point, so that formulae cells must be adjusted, typically with copy and paste. Wishlist: Automatically extend the source data Selection range to contain contiguous nonempty rows upon refreshing a Pivot Table, so that interacting with the Pivot Table Layout dialog box, or the workaround, become unnecessary. Issue: If a named range is used as the source data Selection, extend the named range, or not? Issue: Should this be a user-selectable attribute of individual pivot tables, or of the source range (especially if it is a named range)? For example, a named range may be the source Selection for multiple pivot tables. Then it is desireable to have all such pivot tables extend when new data is appended to the source data range. Steps to Reproduce: 1. Create a source data range containing a few columns in LO-Calc. 2. Create 20 or more pivot tables which depend on this source data range. They should differ in which field values are checked and/or which columns are selected for pivot tables fields, so that each pivot table gives a different summary statistic about the source range. This will be time-consuming, but you only have to set it up once for the life of the spreadsheet. 3. Take a few seconds to add a row to the source data range. 4. Spend several minutes updating all the pivot tables. Have fun doing this repetitive mind-numbing task. 5. Add another row of data tomorrow. Have fun all over again. Wash rinse repeat - this is a daily business activity, time which you could spend more productively. Actual Results: 6. Subscribe to online accounting software. 7. Transfer the data from your LO-Calc spreadsheet to the accounting system. 8. Throw away LO-Calc, and mourn the loss of flexible custom management reporting. 9. Pay monthly subscription fees for the life of the business. Expected Results: 5. Add another row of data tomorrow. 6. Refresh pivot tables. 7. Relax. Reproducible: Always User Profile Reset: No Additional Info: Automatically extend the source data Selection range to contain contiguous nonempty rows upon refreshing a Pivot Table, so that adding data to the source data range does not necessitate working with the Pivot Table Layout.
+1
Use a Database Range for your data and base the Pivot Table on this range. When you want to add data to that range, go to the last row of the range and click on its row number. From context menu use "Insert rows below". I know it sounds silly to "insert a row below" if the cells are already empty. But this action extends the range. Now you can write your data into the empty cells. Refresh of the Pivot Table considers the newly entered data this way. You can test that this works, when you use "select database range" before and after.
(In reply to Regina Henschel from comment #2) > Use a Database Range for your data and base the Pivot Table on this range. > When you want to add data to that range, go to the last row of the range and > click on its row number. From context menu use "Insert rows below". I know > it sounds silly to "insert a row below" if the cells are already empty. But > this action extends the range. Now you can write your data into the empty > cells. Refresh of the Pivot Table considers the newly entered data this way. > > You can test that this works, when you use "select database range" before > and after. This sounds plausible and even makes sense from the point of view of working with a database. But inserting rows below the table sounds "hackish". If I recall some 30 years ago (or I might be dreaming), MS Access has a permanent empty row at the bottom of a table, with a row number like "*" which is not part of the table data until something is typed into it (and then it receives a real row number, and a new empty "*"-numbered row appears after). LO-Calc does not have the luxury of infinitely extensible tables, but neither does Access. Could we do something like Access - have a special-purpose empty last row in a database range which is part of the range only for the purpose of adding data and extending the database range (and thus also pivot table ranges bound to it), but otherwise exclude the special row from database operations and pivot table operations? And if the so-extended database range runs into non-empty cells the database table would be "full" and deny new data entry until rows are inserted between the database range and the content below, or the content below cleared away. There's a precedent for special row treatment for pivot table source data - the first row of the source range contains field names. So it's not unreasonable to have special treament for the (empty) last row of a pivot table range if it's a database range. The proposed enhancement as originally stated does away with the need for a database range, by sensitizing pivot tables to nonempty cells adjacent to the bottom of the source data range. Of course this might break existing spreadsheets, and there must be a popup confirmation about extending the pivot table source data range (which could be disabled by the user). I'm not denying that using a database range with pivot tables could add value, especially with automatic range extension as discussed above. I even imagine that binding a pivot table to a source range could automatically make the source range into a database range. Would this paradigm shift break anything? If so, then would it be reasonable instead to have a chechbox in the Pivot Table Layout dialog to make the pivot table source range into a database range if it isn't already? The checkbox would show the current state of the range as a database, so if it's already a database range it would appear checked when the dialog is opened. Just an idea to explore.