Ask Your Question

Filters across all sheets in a calc spreadsheet [closed]

asked 2018-01-20 16:25:32 +0200

mistertdba gravatar image

updated 2020-08-03 12:46:05 +0200

Alex Kemp gravatar image


One of my calc spreadsheet contains many sheets. All sheets have a common ID field. I would like to reference a cell containing a value, which is used to filter all ID fields in every sheets in the calc spreadsheet.

Is this solution possible or do I need a Macro?

Thank you for your support.

edit retag flag offensive reopen merge delete

Closed for the following reason question is not relevant or outdated by Alex Kemp
close date 2020-08-03 12:46:25.839081


As I understand it, a filter is something like a SELECT WHERE statement returning its findings (by default) by hiding rows not complying with the WHERE clause.
In what way should a filter return the filtered set taken from many sheets?
The search range would need to be replaced by a cuboid, wouldn't it? And you would need to use the option 'Copy results to.'. How possible?
Currently there isn't such a filter, and I doubt the feature is implementable. Please explain.

Lupp gravatar imageLupp ( 2018-01-20 16:42:16 +0200 )edit

Why did your data go to many sheets though being of the same kind again and again? (Otherwise one "field" of data to filter with would reside in one sheet.)
Quoting @mistertdba: "... or do I need a Macro?"
The doubts I expressed might apply also to a "macro" solution. Or are you thinking of a kind of "multiple MATCH in cuboids"? Ok something like that can be done based on custom code if a maximum number of matches is asured. Such a solution will be very inefficient, I'm afraid.

Lupp gravatar imageLupp ( 2018-01-20 16:51:04 +0200 )edit

1 Answer

Sort by » oldest newest most voted

answered 2018-01-20 20:58:38 +0200

Jim K gravatar image

For example, if the data looks like this:

ID Value
~~ ~~~~
1   a
2   b

Then in cells A1 and A2 of a sheet called "FilterCriteria", put the ID to filter like this.


Then select each data range, go to Data -> Filters -> Advanced Filter and specify criteria range as $FilterCriteria.$A$1:$A$2.

The problem with this approach is that advanced filters do not update automatically. So, write a macro to update all of the filters. See

An alternative is to use pivot tables instead of filters.

edit flag offensive delete link more

Question Tools

1 follower


Asked: 2018-01-20 16:25:32 +0200

Seen: 625 times

Last updated: Jan 20 '18