TL;DR: I create a named range and pow a block of cells in a CSE array formula fragments into having that formula separately entered as a CSE in each individual cell.
(Note: Here I write “column” to mean some number of rows in a column, not EntireColumn.)
Scenario: I’m wanting an automatic sort from a histogram table sorted by category to sorting the categories by frequency. I have a helper column by the histogram table that shows the rankings, then I use INDEX(MATCH(…)) in a CSE array formula that fills a new sorted table at H15 the size of the histogram table. Everything works as expected. The CSE formula for the new table is:
=INDEX($D$15:$E$40,MATCH(ROW()-ROW(H15)+1,$F$15:$F$40,0),COLUMN()-COLUMN(H15)+1)
After the fact, I name the histogram table as the named range Histogram, then edit the CSE array formula to use that absolute, global range name instead of a direct absolute reference. Everything works as expected. So now I have:
=INDEX(Histogram,MATCH(ROW()-ROW(H15)+1,$F$15:$F$40,0),COLUMN()-COLUMN(H15)+1)
At this point I do a hard recalculate, and everything still works as expected.
Now, naturally enough, I go to name the helper column Helper. So I mark the Helper column, type Helper in the named range box, and press Enter. Suddenly the CSE table becomes filled only with the result for H15 repeated in every cell. Mind you, I haven’t even edited the CSE table to reflect the Helper named range yet, the CSE just has the direct absolute reference to the helper column.
What is more, at this point I can press ctrl+z and the Helper named range is removed, but the would-be-sorted CSE table remains broken. If I click on any cell in the CSE table the edit line shows that it is a CSE with { }, but I can edit any individual cell.
In other words it appears as if naming a range (I’ve tried other ranges and names, any seem to trigger this) can cause the CSE formula to fragment from its CSE entry range into being a separate CSE in each cell of that entry range. This is not undone by ctrl+z; however, joining the CSE cells back into a single CSE entry range by highlighting them and fake-editing the formula (adding a space at the end) then pressing CSE will restore expected behavior.
Notice that I can also recover expected behavior with ctrl+z, ctrl+z, ctrl+shift+z, because re-applying the successful edit to use the Histogram range name also rejoins the CSE entry cells.
I’ve uploaded the file as it is right before naming the helper column. So for me, at this point highlighting the yellow column then entering Helper in the named range box (upper left) will trigger the fragmentation of the CSE formula in the red sorted table. (Of course, ctrl+z, ctrl+z, ctrl+shift+z won’t recover behavior now because that’s not in the replay list.)
I’ve been able to copy this sheet to another document and replicate the strangeness.
LibreOffice 7.2.2.2 on Linux Mint 20.
Autosort.ods (14.7 KB)