Is there a way to create “sort criterion presets” in LibreOffice Calc? Consider for example the following:
I use this “sorting criterion” quite a lot, but I also change it regularly to others. It is a pain to have to select all these sort keys each time I want this ordering. It would be ideal to save it as a preset.
The only way I know to achieve something of the kind is to resort to user code.
(Option “Enable natural sort” not available this way afaik.)
In principle you can arrange your re-occurring settings in dedicated cell ranges then, and choose one of these ranges to read the parameters from.
The passing of predefined parameters (settings) to UI-driven tools is a general problem.
As long as you only need a small collection of settings, you can try to work with recorded macros. .
Here is what solved it for me (pretty simple and well-hidden):
Click on Insert or Edit Pivot table → Double-click on Row Fields → Options ->Sort by
Once it’s set here it keeps the sort model.
A simple way: before sorting, select the range of cells to be sorted and create DataRange: Menu / Data / Define Range…
The DataRange “remembers” its sort criteria.
Select the sort range including column labels.
menu:Data>Define… give a name to that range.
menu:File>New>Database
[x] connect to existing database of type “Spreadsheet”
[x] Register the database.
Save the database.
Click the “Tables” section. The named database range appears as a table of a database. All the other “tables” represent the used areas of your sheets. You may hide away unwanted “tables” via Tools>Table filter…
Click the “Queries” section and add a new query in design view.
Pick your named db range as source table, double-click all the columns one by one and mark the sort fields as ascending or descending. You may also change the order of columns and you may rename the columns by means of alias names.
Save the query and the database. Close the database and forget it for now. This is a link to your spreadsheet. Nothing has been converted, copied nor imported. Your data are still in the spreadsheet document and nowhere else.
Back to Calc, hit Ctrl+Shift+F4 for the data source window. Browse your database and drag the query icon from the left pane into a blank area (separate sheet).
menu:Data>Refresh updates the sort range.
Of course, all your data belong into a true database but that’s another story.
P.S. I just noticed that the query designer is not flexible enough because the order of sort criteria is the same as the order of columns. No problem. Right-click the query icon and open it in SQL view.
The SQL command (a “formula” that returns an entire table) looks like this:
SELECT "Date", "Category", "Value", "VAT"
FROM "Sheet1"
ORDER BY "Date" DESC, "Category" ASC, "Value" DESC
Simply adjust the order of sort fields in the ORDER BY clause.