[Solved] Exclude the column heading when using auto filter?

Discuss the spreadsheet application
Post Reply
radentEETP
Posts: 7
Joined: Tue Aug 22, 2017 5:37 am

[Solved] Exclude the column heading when using auto filter?

Post by radentEETP »

I want to be able to very quickly show only rows from a spreadsheet where a particular value in column L is 'x' where x is any one of around 8 or so different possible text strings.

Currently, if I click on the column and add an auto filter this cause a downward arrow to appear in the right hand corner of the 1st cell of the column (where my heading is) which if clicked produces a drop down list of all the unique values entered in to that column. As one would want, clicking on such a value hides all the rows in the spreadsheet except those where the chosen value appeared in column L. There's 2 problems though, 1, it hides more than just the data I don't need to see, it hides the column headings as well. 2, when selecting possible filter values from the dropdown list, the column heading itself is a selectable option, which makes sense, since it's a value that appears at least once in the column, but is also confusing and misleading and also if selected will produce filtered results I can know with certainty will never be needed (Only the column headings visible with nothing else).

Is there a way that I can basically define what is an isn't a heading and have it be excluded from auto-filter lists and remain visible at all times even after an auto filter is applied?
Last edited by Hagar Delest on Sun Oct 01, 2017 9:45 pm, edited 1 time in total.
Reason: tagged [Solved].
Open Office 4.1.3
Win 10
User avatar
JohnSUN-Pensioner
Volunteer
Posts: 876
Joined: Fri Jan 14, 2011 1:21 pm
Location: Kyiv, Ukraine

Re: Can you exclude the column heading when using auto filte

Post by JohnSUN-Pensioner »

Just once set "Range contains column labels" in Data - Filter - Standard Filter - More>>
HeadersInFilter.png
I may not have a lot to give but what I got I'll give to you...
Apache OpenOffice 4.1.5, LibreOffice 6.4.4.2 (x64) on Windows 7
If you think that I did not answer your question, make allowances for my imperfect English
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Can you exclude the column heading when using auto filte

Post by Villeroy »

Column headers are detected automatically if the first row consists of text only, with no number, nor blank. Put some text into every cell of the first row.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Bill
Volunteer
Posts: 8932
Joined: Sat Nov 24, 2007 6:48 am

Re: Can you exclude the column heading when using auto filte

Post by Bill »

radentEETP wrote:Is there a way that I can basically define what is an isn't a heading and have it be excluded from auto-filter lists and remain visible at all times even after an auto filter is applied?
When you turn on the Auto Filter for a range that doesn't have the first line defined as a header/label row, a message should appear asking if you want the first line to be used as a column header. Answering "Yes" excludes the data in the first line from the filter list. Answering "No" includes first line data in the filter list.
AOO 4.1.14 on Ubuntu MATE 22.04
radentEETP
Posts: 7
Joined: Tue Aug 22, 2017 5:37 am

Re: Can you exclude the column heading when using auto filte

Post by radentEETP »

Thanks guys, unfortunately despite all your suggestions I'm as yet unable to get this to work as I'd like.
JohnSUN-Pensioner wrote:Just once set "Range contains column labels" in Data - Filter - Standard Filter - More>>
HeadersInFilter.png
In your screenshots, it looks as though you were able to apply a standard filter (rather than an auto filter), without specifically defining a condition, therefore adding a drop down list on the column heading behaving exactly as the auto filter does. That's what I want, because I'd want the spreadsheet to be easily accessible for anyone to use themselves, hopefully autofilters I apply would survive on someone else using the file but even if not, I expect them to have just enough skill to select a column and click 'auto-filter', which is achieved in a similar manner in excel as well. Beyond that, I think they'd struggle with the standard filter. If your method worked for me as it does in your screenshots it'd be great, but it doesn't. When I apply a standard filter, it expects a condition, which by default is '=' and the value is blank, (not 'isblank', but literally nothing there). Applying such a filter with the range contains column header option checked results in a filter being applied showing all values meeting the condition which is none of them therefore I see the column header, with no filtered values and no drop down list. Any idea how to get this drop down list like you'd have on an auto filter?
Villeroy wrote:Column headers are detected automatically if the first row consists of text only, with no number, nor blank. Put some text into every cell of the first row.
All columns from A1, to the last column of the spreadsheet for which I have values, have column headers consisting entirely of text. Initially there was one column for which the first row was blank but I added the value "text" in to that just to test the theory and it hasn't made any difference sadly.
Bill wrote:When you turn on the Auto Filter for a range that doesn't have the first line defined as a header/label row, a message should appear asking if you want the first line to be used as a column header. Answering "Yes" excludes the data in the first line from the filter list. Answering "No" includes first line data in the filter list.
I noticed that too before I posted, because when I attempted to auto filter the column again but this time excluding the column header in the range by deselecting it, such an option was presented to me, annoyingly though it doesn't seem to do so when I have the whole column selected by clicking the column letter. I don't remember if the option ever was presented to me when I first made the spreadsheet but I do have a sneaking suspicion that the first time I applied the filter to the spreadsheet to test it, the column headings actually behaved as I wanted them to when selecting an auto filter value and the errant behaviour I'm experiencing now is a new phenomenon. Somehow it seems that since an auto filter was once applied previously to the same range it will no longer ask me about column headers AND will default select the WRONG choice in lieu of asking me anything about it.
Open Office 4.1.3
Win 10
radentEETP
Posts: 7
Joined: Tue Aug 22, 2017 5:37 am

Re: Can you exclude the column heading when using auto filte

Post by radentEETP »

JohnSUN-Pensioner wrote:Just once set "Range contains column labels" in Data - Filter - Standard Filter - More>>
HeadersInFilter.png
Ok, just an update, I actually did figure this out based off an interpretation of your suggestion but why it works this way and why the usual prompt asking about including column headers or not was skipped is beyond me. I was having the issue of applying a standard filter that wouldn't filter anything at all unless a value was selected from a drop down list which I couldn't get around and hence my last reply. However I didn't notice until after I wrote that that the option to select a standard filter is accessible from more than one place, there's the 'data' menu from the top bar, but there's also the option to choose it from the drop down list of an already auto filtered column. I selected all rows in a column to which there was already an auto filter applied, opened the drop down list, selected 'standard filter', checked the check box you showed me (which was unchecked, unlike the default for a newly applied standard filter and then clicked okay. The default condition and value triggers were set so as before everything was filtered out except the column header but the difference was there was still the drop down list available. I was able to click the drop down list and select 'all' to bring back all my values, and the list now excludes the column header as a filter option and leaves it at the top when filtering is applied.

Seems kinda weird that I had to do that but at least it can be made to work as I expect, hopefully if I had to send it to anyone they wouldn't have to go through the same thing.
Open Office 4.1.3
Win 10
User avatar
JohnSUN-Pensioner
Volunteer
Posts: 876
Joined: Fri Jan 14, 2011 1:21 pm
Location: Kyiv, Ukraine

Re: Can you exclude the column heading when using auto filte

Post by JohnSUN-Pensioner »

The fact is that filtering parameters (Case sensitive, Range contains column labels, Regular expression...) are common for both filter types - autofilter and standard filter. And they will remain until the change. That is, whenever you apply a filter as defined once before will be used. If I'm not mistaken, these values will be saved in the document. That is, when your book is opened somewhere on another computer, the auto-filter will be launched with the parameters that you asked. However, the user can change them (and again "spoil" the filter)
In my advice, I did not propose to use the standard filter. I just showed one way to get to setting the filter parameters. And yes, you are right - these settings can be seen in other ways.
It seems to me that your problems started when you started to allocate a separate column before running the filter. Just try NOT to select anything. Let the cursor stand on any cell of your data and thus nothing will be selected. As far as I know Calc, it will try to determine about of what range the filtration you are asking. And yes, if the option "Range contains column labels" is currently disabled, it will ask if you want to use the first line.
I may not have a lot to give but what I got I'll give to you...
Apache OpenOffice 4.1.5, LibreOffice 6.4.4.2 (x64) on Windows 7
If you think that I did not answer your question, make allowances for my imperfect English
Post Reply