There is some unusual relation between Data Sort and Data Subtotals that can cause minor issues under certain circumstances. Here is an illustration: * Create a new spreadsheet. * Start in A1 and enter several names going down. * Now start in B1 and enter several matching numbers going down. * Data-> Sort and check that it automatically selects all your data. * Also note in Options that "Range contains column labels" is unselected. * Perform the sort for column A. * Now insert a row before row 1 for a header row. * Add appropriate headers, like "Name" in A1 and "Dollars" in B1. * Data-> Subtotals * Note that the first row is not included in the automatically selected range. At this point is is impossible to perform the data subtotals correctly and you have to cancel the whole operation. The workaround is to select the range manually, BEFORE activating Data-> Subtotals. I have verified this is a regression from OpenOffice 3.2.1 which did not have this problem.
Hi crxssi, following your instructions, works right for me with: Win7x64 Ult. Version 4.0.1.2 (Build ID: 84102822e3d61eb989ddd325abf1ac077904985).
Thanks for testing. Not sure why you could not replicate. I don't think it would be platform specific.... I just tested it again, following my instructions: Broken on LibreOffice 4.0.0.3 under RHEL 6.2. Broken on LibreOffice 4.0.0.3 under Mandriva 2010.0 Broken on LibreOffice 3.5.7.2 under Mageia Linux 2 Not broken on OpenOffice 3.4.0 under Mageia Linux 2 Not broken on OpenOffice 3.2.1 under RHEL 6.2
I've been able to confirm that this bug appears as far back as LibreOffice 3.4.3 using Windows XP. Here is another way to reproduce the error: * Start a new spreadsheet * Starting with cell A1, enter some data into column A, and numbers into column B * Data -> Sort * Sort by any criteria, just hit "OK" * Insert a new row 1 * Add headings to row 1 (something like "Name" and "Age") * Click around to make sure nothing is selected * Data -> Subtotal Notice that the actual header rows are ignored and the two items listed in the box "Calculate subtotals for" list the first row of DATA instead of the headers like they should. It appears that whenever the Subtotals function is called, it's limiting the selected region of data, probably to cut out the header row. But the actual Subtotal function is already ALWAYS cutting out the header row. It's impossible to run a subtotal on a section of data that doesn't include a header row, but that's another story.
Works fine, even clicking out of data, before sort after insert row 1, or before call subtotal. And also works fine in: OpenSUSE 12.2 LibreOffice 3.5 build-413
(In reply to comment #4) > Works fine, even clicking out of data, before sort after insert row 1, or > before call subtotal. Somehow I think you are just doing something differently than I am. Guess we will have to wait for more than just three people's experiences.
Created attachment 75745 [details] Subtotal/sort bug spreadsheet with macro I've attached a spreadsheet with a macro to show the behavior. All you need to do is click the button labeled "Run Macro" and then open the subtotal dialog to notice that the headings are not being used. You should notice that the categories listed in the subtotal dialog are taken from the actual data and are not using proper headings.
Created attachment 75754 [details] Screenshot This is what I see after run your macro and select Menu/Data/Subtotals.
I think i've found why this "bug" is reproducible or not within same version by different users. Depends on profil : Tools > Options > LO Calc > General, "Expand references when new columns/rows are inserted" Reproducible if checked Not reproducible if not checked So i think this is a normal behavior. Range remains the same if the option is not checked. May be we can close this bug ?
GerardF, Changing that setting does indeed explain the difference in behavior that we're seeing. I'm getting the opposite behavior that you indicated. These were my results: Reproducible if not checked (LO Default) Not reproducible if checked I've tested these results using LibreOffice 3.6.5.2 on Arch Linux (current) and LibreOffice 4.0.0.3 on RHEL 6.1 I have 2 issues with this bug being closed: 1. "Expand references when new columns/rows are inserted" is disabled by default. Enabling this feature can cause other unintentional side-effects. If enabling this option was preferred, it probably would be enabled by default. 2. Whether there is a work-around or not, it is inconsistent and buggy behavior. When you call for a Subtotal, THAT is when it should be figuring out the proper data range, not based off of some out-dated/incorrect range that was calculated previously (when the row was inserted?) How can this be the closed as the intended default behavior?
"Expand references when new columns/rows are inserted" might change the behavior, but based on my understand of that option, it really shouldn't. From the help documentation: "Specifies whether to expand references when inserting columns or rows adjacent to the reference range. This is only possible if the reference range, where the column or row is inserted, originally spanned at least two cells in the desired direction. Example: If the range A1:B1 is referenced in a formula and you insert a new column after column B, the reference is expanded to A1:C1. If the range A1:B1 is referenced and a new row is inserted under row 1, the reference is not expanded, since there is only a single cell in the vertical direction. If you insert rows or columns in the middle of a reference area, the reference is always expanded." That all has to do with inserting rows and columns and having it affect/adjust existing references/formulas. In my reported bug example, at the point before the subtotals function is activated, there are no formulas or references at all. And no rows or columns are being inserted by the subtotal function. The fact that the "Expand References" option affects the behavior at all is very curious (and helpful observation), but it is not really a solution, nor does it explain why this is happening. Plus, that option is not selected in OpenOffice when I tested there, and yet there were no such issues with subtotaling in OpenOffice, just LibreOffice... Also, based on the description of the "Expand References" option, I would never want that option "on", anyway. I am guessing most people would not want that "on".
Tested again in LO 4.1. There has been no change in behavior with this issue. Needs at least to be marked "confirmed"
Due to comment 3 updating version
Use of freedesktop.org services, including Bugzilla, is subject to our Code of Conduct. How we collect and use information is described in our Privacy Policy.