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.
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.
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.
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 Calc macro to refresh an advanced filter, or formula to workaround?.
An alternative is to use pivot tables instead of filters.