Filter on part of a column only

Discuss the spreadsheet application

Filter on part of a column only

Postby Phillip » Fri Jun 12, 2015 1:44 am

I wanted a filter on part of a column only: e.g. cells J8:J505 where J8 is the column heading.
I selected cells J8:J505, then Data>Filter>AutoFilter. This appeared to work correctly as it placed the dropdown box on cell J8.
However selecting the filter shows that all subsequent cells, J506:J513, are also included.
This is a real nuisance as my spreadsheet is my personal accounts, with cells J8:J505 containing my account code, of a limited set of values: "b" for bank, "c" for credit card, "s" for cash.
Subsequent J cells - J506:J513 contain comments and cross-checks, which I do not want to filter and the values they hold make the filter selection look a mess.

Is it possible to exclude J506:J513 as I did with cells J1:J7?
or do I need to ensure all subsequent J cells are blank?
i.e. is this a limitation of Calc or is it a bug?
Open Office 4.1.7, Windows 7 Professional 64-bit
Phillip
 
Posts: 84
Joined: Wed Jan 09, 2013 1:50 pm

Re: Filter on part of a column only

Postby acknak » Fri Jun 12, 2015 2:33 am

Phillip wrote:... However selecting the filter shows that all subsequent cells, J506:J513, are also included. ...

This is what I see also. No matter what I do, the autofilter always filters the entire data table. Even setting a database range doesn't restrict it.

However, the standard filter (Data > Filter > Standard Filter ...) seems to use only the selected area, if there is one.
AOO4/LO5 • Linux • Fedora 23
User avatar
acknak
Moderator
 
Posts: 22756
Joined: Mon Oct 08, 2007 1:25 am
Location: USA:NJ:E3

Re: Filter on part of a column only

Postby MrProgrammer » Fri Jun 12, 2015 3:44 am

Phillip wrote:I selected cells J8:J505, then Data>Filter>AutoFilter. This appeared to work correctly as it placed the dropdown box on cell J8. J506:J513 contain comments and cross-checks, which I do not want to filter and the values they hold make the filter selection look a mess.
Move J506:J513 to J507:J514, leaving J506 empty. The empty cell ends the autofilter range on my version of Open Office..

If this answered your question please go to your first post use the Edit button and add [Solved] to the start of the title. You can select the green checkmark icon at the same time.
Mr. Programmer
AOO 4.1.7 Build 9800 on MacOS 10.14.6.   The locale for any menus or Calc formulas in my posts is English (USA).
User avatar
MrProgrammer
Moderator
 
Posts: 3902
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

Re: Filter on part of a column only

Postby Phillip » Mon Jun 15, 2015 4:17 pm

Sorry MrProgrammer, my range J8:J505 already had blanks in J8, J505, and J506. The only solution I find is to keep all subsequent cells in col J blank. Deleting subsequent cells: J506: on updated the Autofilter selection list.

However further problems:
When I initially selected J8:J505, with J8 blank, CALC asked if J8 should be the header cell - Yes.
Subsequently I entered a value in J8 - this was added to the selection list. (Not the case if Autofilter on whole column).
Selecting any other filter value resulted in J8 row being hidden. CTRL+Z recovers, but also undo's all edits while filter on.
Deleting J8 did not correct, nor did deleting whole Autofilter
(Laborious as need to reselect whole range J8:J505, Data>Filter>Autofilter as it is a toggle)

Why is Data>Filter> "Remove Filter" always greyed out?

Also CALC started adding additional Filters both in col J above within my range, and even worse in other columns!

In trying to reproduce how, ended up with a corrupt file (Write error) - recovery failed too. Luckily working on a copy.
NB. Cannot reproduce either fault of on a clean file!
Open Office 4.1.7, Windows 7 Professional 64-bit
Phillip
 
Posts: 84
Joined: Wed Jan 09, 2013 1:50 pm


Return to Calc

Who is online

Users browsing this forum: No registered users and 16 guests