Ask Your Question
0

Filters across all sheets in a calc spreadsheet

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

mistertdba gravatar image

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?

Thank you for your support.

edit retag flag offensive close merge delete

Comments

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
0

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.

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.

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower

Stats

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

Seen: 185 times

Last updated: Jan 20 '18