Calc: multiple alternative filters?

[chrisjj]

My sheet has a “Day” column and rows containing in that column “Monday am”, “Tuesday pm” etc.

I want to be able to filter the view to show just rows having Day start with a given day e.g. “Monday”. Standard filter can do this. But only for one day.

Is there any way to do this for all days, letting me pick day on few taps/clicks? Multiple stored standard filters settings sets would work, but the program doesn’t have that.

Thanks.

PS My current workaround is a separate column extracting the first word (=IFERROR(LEFT(B4, FIND(" ", B4)-1), B4)) then use the regular Autofilter, albeit then to requires switch to a new day, I must to untick the old day as well as tick the new day.

[dscheikey]

Hello chrisjj,

you have made several mistakes when creating the special filter.

1. The column header must be part of the filter.
2. The way you have written it, a cell should fulfil all three conditions. Monday, Tuesday and Wednesday
3. The regular expression must also recognise the part after the day of the week. So ‘.*’ is added after the day of the week.

I have made you a sample file in which I have prepared three days of the week as a named range.

Please copy it.

Ask_126016_chrisjj.ods (9,3 KB)

[Lupp]

Compounds like "Monday pm fishing (no success)" are devil’s pets.
Local attributes of dates (like the names of weekdays) are no data.
If you want them displayed, always give the DateTime in a way that it can be converted by standard processes to the standard representation by numbers. How such a value is displayed is then a matter of formatting.

See attached example.

disask126016_StrangeFiltering.ods (26.2 KB)

[Wanderer]

@chrisjj

Is there any way to do this for all days, letting me pick day on few taps/clicks?

Yes, there are also advanced filters:

https://help.libreoffice.org/latest/en-US/text/scalc/01/12040300.html

[mariosv]

And advanced filters are very powerful on using Named Ranges, marked to use with filters, so every named range can contain a filter, and they can be selected by the named range in the advanced filter window.

[chrisjj]

Thanks. Whenever I try Advanced Filter and enter a range, I get an error about the range being invalid. I have read the Help. Is there a minimal example I can start with?

[mariosv]

Then, attach a sample file with what you want to do or what you have tried.

Have you defined the named ranges, including the column titles for the fields to filter?

[chrisjj]

Have you defined the named ranges, including the column titles for the fields to filter?

No. I tried selected range.

I tried:

and got erroneous results:

I expected all rows.

How to fix please?

[mariosv]

Again, pls attach a sample file.

[chrisjj]

Again, pls attach a sample file.

Sorry. Missed that. Here it is, plus screen pics.

0 Book.ods (14.3 KB)

EDIT: Restoring pic names omitted by the forum upon post by email.

1 Content - hopefully as per Help.png

2 Filter showing - OK.png

3 Filter unexpected inclusion of A4 - FAIL.png