# Filters across all sheets in a calc spreadsheet [closed]

Hello,

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?

edit retag 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.

( 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.

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

Sort by » oldest newest most voted

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.

ID
~~
1


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 https://ask.libreoffice.org/en/questi....

An alternative is to use pivot tables instead of filters.

more