Ask Your Question
0

Prevent Calc from sorting column labels with data using AutoFilter?

asked 2014-03-25 22:29:16 +0200

stuckfly gravatar image

updated 2015-08-23 19:06:24 +0200

Alex Kemp gravatar image

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?

edit retag flag offensive close merge delete

Comments

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.

steves4892002 gravatar imagesteves4892002 ( 2015-06-12 17:54:23 +0200 )edit

7 Answers

Sort by » oldest newest most voted
1

answered 2014-03-26 01:00:10 +0200

m.a.riosv gravatar image

updated 2014-03-27 21:43:53 +0200

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... .

If you can't resolve, try resetting the user profile: https://wiki.documentfoundation.org/U...

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.

image description

edit flag offensive delete link more

Comments

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?

stuckfly gravatar imagestuckfly ( 2014-03-26 22:04:33 +0200 )edit

You can find all versions here: http://downloadarchive.documentfounda...

m.a.riosv gravatar imagem.a.riosv ( 2014-03-26 22:07:12 +0200 )edit

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?

stuckfly gravatar imagestuckfly ( 2014-03-26 23:29:56 +0200 )edit

Verify if the selected range is right and the options in Menu/Data/Sort are also right.

m.a.riosv gravatar imagem.a.riosv ( 2014-03-27 00:17:47 +0200 )edit

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:)

stuckfly gravatar imagestuckfly ( 2014-03-27 01:25:41 +0200 )edit

Verify if there are overlapped data ranges in Menu/Data/Ranges.

m.a.riosv gravatar imagem.a.riosv ( 2014-03-27 01:45:00 +0200 )edit

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).

stuckfly gravatar imagestuckfly ( 2014-03-27 02:13:03 +0200 )edit

Some cell in the problematic sheet with a different language?

m.a.riosv gravatar imagem.a.riosv ( 2014-03-27 02:26:42 +0200 )edit

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…

stuckfly gravatar imagestuckfly ( 2014-03-27 04:17:48 +0200 )edit

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?

m.a.riosv gravatar imagem.a.riosv ( 2014-03-27 19:06:30 +0200 )edit
1

answered 2015-08-02 22:18:42 +0200

Alanterra gravatar image

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.

edit flag offensive delete link more
0

answered 2014-07-03 10:22:15 +0200

geert gravatar image

updated 2014-07-03 10:29:07 +0200

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....

edit flag offensive delete link more

Comments

I have a minimal file that exhibits the behavior for me.

Instructions:

1) Select "Sort Ascending" from the "Start" column pulldown
2) Observe that the column headers, and pulldown controls, get sorted with the data.

Version 4.0.0.3.

Screenshot:

Test file

Screenshot

gbell12 gravatar imagegbell12 ( 2014-07-04 23:13:09 +0200 )edit
0

answered 2015-11-16 03:12:11 +0200

KeithF gravatar image

I was having this same problem today. I was working on data imported from html in one worksheet, and on data exported to xlsx in a second worksheet. The former would sort the headers with the data, the latter did not.

As it turns out, when Calc imports data from html, it does strange stuff with columns - for example, a column of data would show as being in both column C and column D. Once I got rid of the phantom columns and deleted any remaining columns with no data in them, the column headers stopped being sorted.

edit flag offensive delete link more

Comments

You could try to import html tables trough Menu/Insert/Link to external data, after enter the url wait a bit, then you can select the table to import.

m.a.riosv gravatar imagem.a.riosv ( 2015-11-16 21:56:54 +0200 )edit
0

answered 2014-05-04 13:20:56 +0200

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.

edit flag offensive delete link more
0

answered 2015-06-15 19:49:03 +0200

arop gravatar image

Make sure that all the headings are filled in. Blank headings do cause this problem for me.

edit flag offensive delete link more

Comments

+1 I have been pulling my hair out over this as well. A bit of tinkering suggests that headings must contain non-empty text. E.g a numeric entry that just happens to look OK isn't good enough. It has to be '-prefixed to make it text.

53ESfAZp gravatar image53ESfAZp ( 2015-08-03 22:10:25 +0200 )edit

I wanted to upvote this, but I have no rep.

TecBrat gravatar imageTecBrat ( 2017-04-15 01:41:10 +0200 )edit
0

answered 2014-10-09 00:39:02 +0200

In My case the problem was that the top row contained some labels with numbers and % sign (actually cell's format was Percent) I had to name that cell with the word (along with numbers, cause I need it so), and everything works fine. When sorting ranges, the Labels stays up top.

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2014-03-25 22:29:16 +0200

Seen: 11,307 times

Last updated: Aug 02 '15