Ask Your Question

Advanced filter search for any of list of names [closed]

asked 2018-05-20 21:15:33 +0200

spooler gravatar image

updated 2020-08-18 13:23:56 +0200

Alex Kemp gravatar image

I'm trying to filter using a single column table of about 30 names (headed "NAME") as a search criterion, the idea being to retrieve all the rows from another large table where the "NAME" column contains one of those 30 names. Really a database operation, but calc can nearly do it..

It works OK with 8 names or fewer, but I've discovered there's a limit of 8 rows to the criterion. Can anyone suggest an alternative approach using calc? Thanks, Peter

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2020-08-18 13:24:14.639264

2 Answers

Sort by » oldest newest most voted

answered 2018-05-20 23:53:22 +0200

updated 2018-05-20 23:54:22 +0200

Advanced filter supports up to 8 search criteria, if you want to filter data only by name, it would be just one criteria with multiple values in it. When you create table with criteria for advanced filtering it means that you can have up to 8 columns with test conditions (those are logically connected with AND operator), but you can fill each column with much more than 8 values (those are logically connected with OR operator) So just create one column for NAME filter and populate it with all the names you want to include in the result set. Nice idea is to Define range for the source data and for the filter result data, and Named range for Filter criteria - makes work easier ;)

image description

edit flag offensive delete link more


Thanks for your reply. Unfortunately I don't have the kudos to upvote it. I read in documentation that there was a limit of 8 criteria, but I thought this meant 8 rows, not columns, in the filter definition.

spooler gravatar imagespooler ( 2018-05-21 12:06:12 +0200 )edit

answered 2018-05-21 12:10:07 +0200

spooler gravatar image

Ah - I was trying to do this in Open Office's version of calc - where it really won't accept more than 8 rows in the filter definition. If you have more than 8 it comes up with an error box. It works with more than 8 in libre office - nice. Sorry to bother you with Open Office's problems!

edit flag offensive delete link more

Question Tools

1 follower


Asked: 2018-05-20 21:15:33 +0200

Seen: 2,994 times

Last updated: May 21 '18