How to protect the results of a formula so it survives later sorting - LibreOffice Calc

[GSArider]

I present a music show on the radio. I have a LibreOffice spreadsheet listing every track I play each week, with columns showing Artist, Song title, and the date I played it. Every few weeks it’s possible I might play a track again, but I want to see at a glance if I’ve played a certain track recently. I need to avoid playing a song one week, and then playing it again the following week.

I have created a column entitled ‘Multiple Plays’, and if a song has been played more than once (detected by a formula in every row of that column which looks to see if the previous and/or next cells contain the same song title) it places “MP” in the Multiple Play column. Most of the time I have the spreadsheet sorted thus: Artist, then Song Title, then Date Played. That way I can quickly find a particular song, because the catalogue is alphabetical.

However, if I then sort the whole spreadsheet so that it’s ordered: Date Played, then Artist, then Song Title in order to see show-by-show which tracks I played, the ‘MP’ formula now doesn’t work (because now the previous and/or next rows don’t contain the same song title because I wouldn’t play a song twice in the same show) and the ‘MP’ column is therefore blank.

What I want is a way of making it so that when the sheet recognises that a song has been played more than once, it places some kind of permanent tag or marker on the song title itself (or just something visible in another cell in the same row), which then stays with it even if I sort the sheet in different ways. As time goes on, more and more tracks would become tagged, and there would be fewer tracks that I’d only played once. That’s exactly what I want.

Is this possible? If so, how?

Your thoughts would be much appreciated.

Nick

[Hrbrgr]

IMHO, this is yet another application that calls loudly for a database.

[eeigor]

I don’t know how you sort there, but this is a array formula and this column should not be included in the sort range.
The way of marking of tracks is conditional formatting.

[erAck]

this? I doubt that this (MP?) is an array formula, however, we don’t have a sample document.

Anyway… it may be that for this specific case (relative references pointing to other rows within the to be sorted range shall keep their relativity) the questioner wants to enable Tools → Options → Calc → General, Update references when sorting range of cells. It may also be that absolute cell references are to be used with the same option if they shall keep pointing to the same cells after the sort. Again, we don’t have a sample so it’s hard to tell (@GSArider you may want to upload / attach one to your question; edit (under the three ... dots and then pencil) and use the Upload icon).

[Villeroy]

Doing this with a spreadsheet is nonsense.

[Villeroy]

And this is why: songs.odb (89.4 KB)
EDIT: Just for the fun of it, I uploaded a new version recording the minutes and seconds of the songs and summing up the times per show.

A quick database draft with a few hundred dummy titles and artists in a song list and a table of performances (shows).
The “Shows” form lets you enter a show date, a number of days to be skipped and a case-insensitive search string. After hitting the [OK] button you get a list of songs that have not been played since [skip days] filtered by the given search string (if any). The list on the right side shows songs that have been added to that show. Add more songs from the green list and click the refresh button from time to time. No auto-refresh yet because this is just a quick draft free of macros.
The report gives a printable play list.
The other form is for the list keeping of availlable songs in the songs table. Duplicates with same title and artist can’t be entered.

[Lupp]

Without seeing your formulas it should be hard to give a reasonable answer - not to speak of a solution.
Your issue may be one related to the conflict caused by the expectation a formula should return something defined by its role (most recently played e.g.) though by sorting the position (cell address) of the respective item was changed in an unforeseen way. However somebody may expect differently where the item isn’t identified by such a role but by the plain content of a cell referenced in one or another way.
Some years ago there was a related dispute caused by a change of the behavior of Calc regarding the question whether or not references should be “updated” when sorting. As a result of that discussion there now is an option “Update references when sorting range of cells” under >Tools>Options>LibreOffice Calc>General which by default is disabled. In rare cases it may be reasonable to enable it for specific spreadsheet documents. Be careful. It’s not exactly simple to understand the implications.

[WhiteKnight]

@GSArider

I have created a column entitled ‘Multiple Plays’, and if a song has been played more than once (detected by a formula in every row of that column which looks to see if the previous and/or next cells contain the same song title) it places “MP” in the Multiple Play column. Most of the time I have the spreadsheet sorted thus: Artist, then Song Title, then Date Played. That way I can quickly find a particular song, because the catalogue is alphabetical.

One quirk of CALC sorting with updated cell references is that it doesn’t recognise ranges embedded in parentheses viz…(C1:C3) so it’s better to identify the three target cells (C1;C2;C3) then if they end up as (C12;C45;C75) they’re recognised by the “adjusted” formula but (C41:C43) simply returns three contiguous cells starting at C41

[fpy]

@lupp could you come up with a 2 liner to complete https://gerrit.libreoffice.org/c/help/+/164625 ?
thanx

[Lupp]

Sorry!

@Lupp

Be careful. It’s not exactly simple to understand the implications.

Read this as: “I don’t feel sure to understand them myself.”
Moreover I don’t feel sure if the original implementer of the behavior “Update references when sorting range of Cells” had understood every aspect.
Moreover I don’t feel sure if the OP here knew exactly how “protect the results of a formula so it survives later sorting” should be interpreted in every thinkable case.
…
And to tell what I think I have understood would not fit into a two-liner.
My personal solution was to never use the mentioned option.

The two one-liners I might offer:
Sheets as you describe are an attempt to create a DataBase without DataBase software.
Never sort original data if more than one sorting shall be available! Create indexes for “sorting access” instead.

I’m NOT an expert insofar.
And the fact that the missing help still is not offered should be interpreted as:
Either “nobody knows” or “reliable help would need much too many words”.
Ask the Assignee to tdf#113231 .