Page 1 of 1

Filter on part of a column only

PostPosted: Fri Jun 12, 2015 1:44 am
by Phillip
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?

Re: Filter on part of a column only

PostPosted: Fri Jun 12, 2015 2:33 am
by acknak
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.

Re: Filter on part of a column only

PostPosted: Fri Jun 12, 2015 3:44 am
by MrProgrammer
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.

Re: Filter on part of a column only

PostPosted: Mon Jun 15, 2015 4:17 pm
by Phillip
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!