Make sheet with dynamically sorted version of main sheet

[librebird]

I have a main sheet where I will be adding and changing the data.

I would like to have a second sorted sheet that contains the same data dynamically updated (change a cell on main sheet and the cell is changed on the sorted sheet) but sorted by one of the columns. I don’t plan on ever editing data on sorted sheet.

I have tried selecting the columns from the main sheet and pasting special (into the new sorted sheet) and then choosing options>as links. But this only pastes existing cells. Adding something to the main sheet doesn’t add it to the sorted sheet.

And more importantly, the sorted sheet does not dynamically sort itself when the main sheet is changed.

Is this even possible to achieve? What method should I be searching for?

Thanks!!

[Wanderer]

You asked for Calc, so my hint to solve the problem was for Calc. But it should be mentioned your question describes the standard behaviour for databases.
.
So if you put your data-table in a database, any query can give you another sorting, and you could even drag a query to calc, to have a copy available there for special tasks.

[Villeroy]

The vast majority of all spreadsheet related questions arise from the fact that today’s spreadsheet users try to use spreadsheets as databases desparately.

A possible compromise goes like this:
menu:File>New>Database…
Connect to existing db of type “Spreadsheet”.
Specify the Calc document and check “Register database”.
Save the database.
Database ranges and the used are of each sheet is represented as a table in a pseudo-database.
Create a query with the wanted sort order(s) and filter(s).
Save and close the database.

---

Get a blank Calc sheet, hit Ctrl+Shift+F4 and drag the query into the sheet.
This pulls the sorted/filtered record set into the spreadsheet as a link. Menu:Data>Refresh refreshes the link.
A most simple macro

ThisComponent.DatabaseRanges.getByName("Import1").refresh()

refreshes the database range, for instance when the sheet gets activated.

[Wanderer]

Copy the data could be done by using filters - you can direct the output to other sheets and update later, but for an auto-update you would need a macro. Never tried to sort this way ( and as @JohnSUN
showed below this can be done directly in the options of Sort, not filter )
.
I would suggest to check for pivot-tables, usually used to sort and group data. In your case you can maybe omit the grouping.

[JohnSUN]

@Wanderer

Copy the data could be done by using filters

Just a small clarification - copy the data could be done by using sorting (usually this is not mentioned)

[librebird]

@JohnSUN, thanks for that. Unfortunately that makes a copy, doesn’t link them. But it’s an option I never noticed and may make use of if nothing else works out.

[JohnSUN]

Usually you don’t need source cell references, you need a sorted table.
Are you talking about links because you want to get sorted data every time the source data changes? Let’s do it.

Sort the original table, copying the sort results to another sheet. Now arbitrarily change the data in one or more cells of the original table. Select any cell within the source table and choose Data - Refresh Range. Check the sorted table. Are you satisfied with this result?
Now search this resource for messages with refresh.
You will find a lot of tricks associated with this word - macros, link to external data, and much more. The task of automatically updating a sorted table can be solved.

Why doesn’t Calc perform such a refresh by default? If we are talking about a table of two dozen rows, then sorting will happen instantly. But a sheet can contain more than a million rows, sorting such a table can take several seconds. Now imagine that your spreadsheet is frozen for a few seconds after any minor change in the original data. Are you satisfied with this behavior of the program?