Calc macro to refresh an advanced filter, or formula to workaround? [closed]
First time posting a question here—hello! :)
In a nutshell: How can I make a one button macro in Calc to "reapply advanced filter (including copying the results to the defined output range)" so that a non-technical user can easily refresh the needed report? (It doesn't have to be a macro but it seems that is the only plausible solution.)
System details: I'm running LibreOffice 3.4.4 on Mac OS 10.7.4.
Now I'll give the additional details which are usually left out of questions like this, resulting in a zillion back-and-forth exchanges on why all the proposed solutions won't work. :) The basic question is already listed up above.
The situation is too complex for an auto filter or a standard filter and not complex enough to warrant creating an actual database. All that I really need are subsets of the spreadsheet data—I don't need it summarized in any way (or at least not in any way that I can't easily do with Calc). I'm pretty adept with Calc but I've never touched macros at all.
I set up a "user input" section upon which the report is based, complete with drop down lists, and set up the advanced filter criteria based on the user input. The advanced filter works beautifully and I've set it to "copy results to" a pre-defined output sheet. Only trick is, since the filter criteria themselves are based on user input (but cannot be done with just an auto filter), the output sheet from the advanced filter needs to be refreshed (i.e. generated newly based on the changed criteria) after the user makes any change to the drop down user-input section.
It seems likely to me that a macro could be set up to use my already defined "criteria range" and "data range" and "output range" and just apply the advanced filter newly. Maybe this could be tied to a button right next to the "user input" section; whatever. The thing is, I have no idea how to write a macro like that and hunting the web (with a zillion tabs open by now) has turned up absolutely nothing matching that requirement. I'm really hoping that someone here will know how to do that....
Thanks for reading and I hope you can help!
UPDATE: Thank you John Sun! Your non-macro approach is great.
One more issue with it, though—I can't work out any way to sort the output with this approach. Generally I'll want to sort it by student, then by subject, then by date. Sometimes I would use slightly different sort orders, but because all the data cells in the report are dynamic, I can't sort them at all without manually copying first and "pasting special" without formulas. This is why I initially gave up on the formula approach. By contrast, advanced filters give a static output when used with "copy results to...", and so can ...
OK, Wildcard, I'm sure you can solve this problem without using a standard filtering mechanism. There are a few fun tricks.
Thanks! That is helpful—unfortunately, I've already been through those types of approach. I even got a functioning formula for what I need, without standard filtering...and then when I tried to "fill down" LibreOffice hung and I had to force quit. You see, I'm dealing with about 6000 rows of data that I need to filter from.
But still, nice to see that method! I had never seen that specific approach. Cool. :)
I wish I could upload a copy of the file I'm working with for you to see....
How many columns in the table of source data? 6K rows - OK, but how many columns copied to report? How many filter conditions fields? If they are really a lot, then maybe still makes sense to think about the database
It's a spreadsheet of student course completions throughout the year. There are only about 5 filter conditions per report, but since two of them are date criteria (greater than and less than), it winds up with only max about 100 rows in the report, usually [much] less.