Description: When two (or more) arrays are converted to Database ranges and those ranges are indexed with autofilters, then; if the user inadvertently enters data into the unbounded rows between the db ranges - thereby bridging the gap between the two ranges - those ranges become merged and any autofilter action will prove destructive to the entire structure. Even such an insignificant action as selecting the autofilter button will cause the destruction. The range definitions will be amended to show the first range encompassing the area for both ranges and the second range "appears" to have its own boundaries. This is however an illusion. Any attempts to redefine the boundaries of the first range may appear to have been successful but the damage has already been done. The amendment will have zero impact and further inspection will demonstrate that the second range is still subsumed by the first range with the definitions showing the erroneous structure, not the amendments. The test file attached will even demonstrate that undoing all the actions that created the bond, doesn't revert the file to two ranges. It is possible to remediate by manually selecting the second range and moving it to a location outside the "global range". At this point, the definition for the second range will probably disappear. The first range can be redefined to the original parameters and the second range can be moved back to the original location. The surplus lines in the first range can simply be deleted or the definition can be amended as there is no data in the extension. In my experience, the ranges may lose their integrity so it's wise to actually delete the definitions and start afresh. The symptoms I have observed when the ranges are first separated are:- One range definition may actually disappear The autofilter buttons may still be present but inoperative Steps to Reproduce: With the attached file Observe the db range definitions MENU>Data>Define Range - select each range and confirm Select the autofilter at B3 and observe it only indicates the first range Repeat at B12 Enter data at B10 and select the autofilter at B3 to verify Enter data at B11 and select the autofilter at B3 to verify Observe the data now includes all of the second array Try deleting the data in B10:B11 Observe that autofilter B3 still includes the second array Try editing the definitions to repair the array and observe the futility. Try saving, closing, opening and then autofilter B3 - still corrupt. Select B12:E18 and relocate the array to H25 verify the extent of the first array and either edit the definition or simply delete the rows until the definition only encompasses B3:E9 Select the second array and relocate to its original location. The probability is that you will need to redefine the autofilter for at least one array Both ranges should now perform as expected Actual Results: Two arrays destructively merged into one Expected Results: Discrete arrays Reproducible: Always User Profile Reset: Yes OpenGL enabled: Yes Additional Info: Version: 7.2.2.2 (x64) / LibreOffice Community Build ID: 02b2acce88a210515b4a5bb2e46cbfb63fe97d56 CPU threads: 4; OS: Windows 10.0 Build 19043; UI render: Skia/Raster; VCL: win Locale: sv-SE (en_GB); UI: en-GB Calc: threaded
Created attachment 176636 [details] Example file
Workarounds: 1 Data Validation of the cells immediately above the header - say minimum of 5000 characters or perhaps a 256bit hash of something 2 Cell protection of the cells immediately above the header - ensure this doesn't interfere with any regular sheet operations.
Created attachment 176861 [details] Additional Examples I'm attaching another sheet demonstrating some more unexpected results. In all honesty, I was experimenting to see just what would break it and how. Initially, the sheet was created as defined with the array A3:D18 included within the scope of the DB Range "Assorted" I then created the SUM() on row 2. Initially, I attempted to create the autofilters on row 4 - the numbers, but the process arbitrarily moved this to row 3 - the characters. Could be reasonable as that was the "top" of the defined DB Range I then edited the range to A1:D18 and it automatically created the autofilter on row 1 but did not erase the filter from row 3 - Maybe not quite so reasonable. I then consolidated B1:C1 into a merged cell then the same for B3:C3 - KEEPING the hidden cell contents. I then "froze" row 3 Any attempt to activate any filter creates the information message "Ranges containing merged cells can only be sorted without formats. Unmerging the cells permits filtering on any "active" filter but note that the filter has now been removed from B3:C3 Repeated attempts to re-filter either B3:C3 or A3:D3 with the autifilter MENU button toggles row 1 on & off. The results of any filter activation without the "merged" cells providing a "veto" Reflect the "unusual" structure. and as per the initial report - all the rows become sortable elements within the array. Interestingly, the SUM() function now includes itself with the appropriate circular reference error.
Repro with file. I tested the problem of deleting the extra data not removing the second range from the filter and I noticed it was not present in 3.3.0, but was already in 3.5.0. Arch Linux 64-bit Version: 7.5.0.0.alpha0+ (X86_64) / LibreOffice Community Build ID: d96d1b380d3c626522407397e56d68030a73e02a CPU threads: 8; OS: Linux 6.0; UI render: default; VCL: kf5 (cairo+xcb) Locale: fi-FI (fi_FI.UTF-8); UI: en-US Calc: threaded Built on 25 November 2022