Using LibreOffice version 4.2.0.4
Calc
English
Mac OS 10.7
I have a spreadsheet with many columns, with one row of column labels. I have defined the columns as a database range, with column labels on. I need to use AutoFilter to sort the range, but Calc sorts the column label row into the data range. Is this a bug? Or is there some other option I need to apply for Calc to ignore the column label row when sorting?
I’m on version 4.0.4.2. I had this problem. I noticed I still had the document in csv format, and I had not yet saved it as xlsx format. Once I saved it in a valid format to support the filters, it began to work.
While in any place of data range, go to Menu/Data/Sort - Options, verify if the option Range contains data labels is enable.
There was a bug report, fixed, about it in 3.6, https://bugs.freedesktop.org/show_bug.cgi?id=53482.
If you can’t resolve, try resetting the user profile:
https://wiki.documentfoundation.org/UserProfile#User_profile_location
Attached file works for me.
TestSortLabels.ods
And if you can update your version, there are many fixes in the last.
edited 20140327
Sorry but I can’t reproduce the issue.
Thanks
Menu/Data/Sort - Options, data labels were (and are) enabled for my file. But, if I click the OK button LibreOffice quits unexpectedly. It does not crash if I click Cancel. LibreOffice recovers the file successfully, but it still crashes if I try again. So, a problem with my file?
Reseting user profile did not fix the sorting problem, nor the options crash.
Your attached file works fine, neither of the problems apply to it.
Help/Check-for-updates shows 4.2.0.4 is current. Update how?
Now using 4.2.2.1
I was going to attach a sample of my file, after deleting irrelevant sheets and personal records; but the sample file failed to crash on Sort/Options/OK. I looked closer at options and found it was set to sort rows; not columns, with row labels on. Fixed that in my sample file and the main one. Sort now works in both, no crashes, labels stay at top. No idea why Sort crashed sorting rows.
Autofilter still sorts labels into the data in both my files, but Sort does not. Why?
Verify if the selected range is right and the options in Menu/Data/Sort are also right.
Selected range is the whole table, including column labels (row 1).
Menu/Data/Sort options are correct.
Menu/Data/Define Range shows the table correctly defined also, with the correct options.
It’s like there’s a setting in my file (but not yours) that overrides the column labels option for Autofilter, but not if I use the Sort function. I cannot reproduce the problem in a new file, so I’ve done something odd in my main file.
Autofilter would be nice, but Sort works…
Thanks for working on this:)
Verify if there are overlapped data ranges in Menu/Data/Ranges.
Nope. There’s only one database range defined via Menu/Data for the file. I have several named ranges via Menu/Insert/Names…, but none of them are in the sheet where my Autofilter problem is.
I tried Autofilter on another sheet in the file and it works fine, without defining a range or any sort options (Menu/Data/Sort - Options defaults are the same as for the uncooperative sheet though). So, the problem is only within the one sheet; not the whole file—not a global setting).
Some cell in the problematic sheet with a different language?
All set to “Default-English (USA)” via Menu/Format/Cells… - Numbers.
But…I have two columns with custom formats, wherein the cells that are column labels are formulae whose formats include label text ("$00.00 Pending", where ‘Pending’ is format code; not label text). Oddly, deleting the columns fixes Autofilter, but clearing or changing the cell formats to currency (not custom) or deleting everything in the label cells does not solve the Autofilter problem! Nothing else odd in those columns…
Would be of interest to know the source of the issue, but seems it doesn’t want to show their face :).
Can you do a file minimal file sample with the issue?
link text
The offending sheet is Expenses and I replaced all personal data with garbage text. The two column label cells with custom formats are highlighted yellow.
Thanks for having a look.
Looking forward to learning what obvious thing I overlooked or misunderstood:P
You can’t reproduce it? So, in that screenshot you posted, if you select Sort Ascending the column labels in row 1 don’t end up replaced by a row of data in row 1 and the labels don’t end up sorted into the data? If it doesn’t do that for you with the same file and version… then… it’s the user!?
Sorry @stuckfly, I can see now, it happens even with new versions. Please can you report a bug Reporting a bug and inform here the number FDO#NNNNN
FDO#? …bug number is 76715
I am having exactly this same set of issues with Version 4.4.0.3 for Mac in April 2015. Exactly the same behavior as scootersooz—worked yesterday, didn’t today, can’t figure out how to create a version that works. Yes I do have column labels set in my Data Range. This is extremely frustrating.
Update, August 2015—this bug (and other annoying bugs) have finally caused me to throw money at Microsoft. I have successfully avoided using MS software for 20 years or more. Today I am purchasing Office 360 and uninstalling LibreOffice.
I’m having the same issue. But wasn’t just yesterday, it worked fine then. Cannot understand why all of a sudden, without any change from me, the column headings are now filtered in with the rest of the column data. Am using the latest version on Mac – Version: 4.1.5.3.
Same issue over here on linux fedora 20 with Version: 4.2.5.2 Build ID: 4.2.5.2-1.fc20
Sorting via Data-Sort and checking the column labels option works fine.
Sorting via autofilter does not, it also sorts the column headings.
Opening Data-Sort-Options, checking the column headings checkbox and clicking OK crashses libreoffice calc…
This is on a file that originally was an excel file and was converted to ods, nothing fancy, just a big table. However, if a new blank workbook is opened and some data manually inserted, everything works fine.
Next thing I tried was opening an blank workbook and copy paste from the excel file, again not working…