Created attachment 187874 [details] Sample ODS - Open the attached sample, with column A containing a couple of consecutive merged cells, - In B1, enter: =SUM( - With mouse, start selecting cells from A1, going downwards. => From subsequent cells, only the first part of the merged cell is shown selected, and is added to the formula. Eg.: A1:A4 instead of A1:A6, or A1:A19 instead of A1:A21. Observed using LO 7.6.0.0.alpha0+ (1f373260cf25ca2f36a929e1a27fde00641e5c09), 3.3.0 / Ubuntu.
Currently the title of this bug 155799 is: Selecting merged cells for formula doesn't select the complete merged cell Such sentence might imply that users should be able to select complete merged-cells, as if each merged area should act as one cell. This is an inaccurate description. Users should be able to select _whichever_ range, whether it is (as for attachment 187874 [details]): A1 A1:A1 A1:A2 A1:A3 A1:A4 A1:A5 A2:A3 A3:A5 A3:B3 or whichever else. The fact that there are merged cells does not mean that each merged area should act as one cell for the purpose of a function. For instance, each cell within a merged area could still contain different values, not all of them being shown while the range is in merged status. IOW, _if possible_, the improvement should not be limited to entire merged cells (only), but rather to whichever range the user wants to select as argument(s) of a function. Ideally, users should be able to select whichever range is desired, whether by dragging the mouse, or by keyboard, or by typing the relevant range directly in the inputwin formula bar. Attachment 187874 [details] demonstrates that this is not always as easy as it should be. For attachment 187874 [details], manually editing the formula later-on is the only way to select certain ranges as arguments, otherwise not possible for this case. This should be corrected/improved, whichever the desired range selection. The situation might not be exactly the same when the selection is not related to arguments of a function; for example, when selecting a range in order to copy it or to apply an attribute on the selection.
As ady noted, in this bug's attached sample case the expectation may be to select the entire merged cells; if there were values hidden in merged cells the alleged expectation may not even hold. Gnumeric when merging removes content from merged cells and keeps only first, as in the sample document, so selection goes from A1 to A1:A6 then A1:A9 as expected. If in Calc content was also in A5 and A6 and cells A4:A6 are merged keeping content and saved, Gnumeric even *deletes* content of A5 and A6 when loading that document then, which I consider a bug. What does Excel do in several cases of this?
To be clear, with attachment 187874 [details] we can currently insert a SUM() function in cell B1 and then select (only by dragging the mouse) either: A1 A1:A4 A1:A7 A1:A10 ... or: A4 A4:A7 A4:A10 and similar selections. Currently, we are not able to select, for instance: A1:A2 A1:A3 A2:A3 and many other ranges, whether the desired range is merged or a mix range (partly merged and partly not). Moreover, in the described case, selecting cells/ranges by keyboard (e.g. arrows, shift, control, home, end...) is currently not possible. So, there seems to be potential improvements or fixes, not only limited/restricted to merged cells. Perhaps the current subject of this report should be modified accordingly.
This bug report doesn't intend to solve the whole can of worms with functions performed including hidden cells. You're welcome to open a separate enhancement on that with the points you've raised.
Henry Castro committed a patch related to this issue. It has been pushed to "master": https://git.libreoffice.org/core/commit/0ccb2ace2825015873cbae550acc83a29cfbb18e tdf#155799: sc: fix "UpdateRef"" to update extended merged cells It will be available in 24.2.0. The patch should be included in the daily builds available at https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: https://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Henry Castro committed a patch related to this issue. It has been pushed to "master": https://git.libreoffice.org/core/commit/6ca51786b90270c86e854f85728f77d0ef5cffa6 tdf#155799: sc: abstraction "moveRefByCell" It will be available in 24.2.0. The patch should be included in the daily builds available at https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: https://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Henry Castro committed a patch related to this issue. It has been pushed to "master": https://git.libreoffice.org/core/commit/a58b3984c1ad05d77eaefd424da12d9b833f76b8 tdf#155799: sc: fix formula reference "key up" It will be available in 24.2.0. The patch should be included in the daily builds available at https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: https://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Henry Castro committed a patch related to this issue. It has been pushed to "master": https://git.libreoffice.org/core/commit/3273d44baaf0679a19527e953809b9d61d8e2201 tdf#155799: sc: add getter function "GetEditString" It will be available in 24.2.0. The patch should be included in the daily builds available at https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: https://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Henry Castro committed a patch related to this issue. It has been pushed to "master": https://git.libreoffice.org/core/commit/cdca06306e0d4ae2d9874bc7bbd0290d100d54cc tdf#155799: sc: qa: add unit test "testKeyboardMergeRef" It will be available in 24.2.0. The patch should be included in the daily builds available at https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: https://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Henry Castro committed a patch related to this issue. It has been pushed to "master": https://git.libreoffice.org/core/commit/ef538b5441875cc3af97e25c83ed15b393d9bfce tdf#155799: sc: qa: add unit test "testMouseMergeRef" It will be available in 24.2.0. The patch should be included in the daily builds available at https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: https://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Henry Castro committed a patch related to this issue. It has been pushed to "master": https://git.libreoffice.org/core/commit/e4e080086e76a0bedd7ce62dc610889dc5c86401 tdf#155799: sc: fix paint the reference mark It will be available in 24.2.0. The patch should be included in the daily builds available at https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: https://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Henry Castro committed a patch related to this issue. It has been pushed to "master": https://git.libreoffice.org/core/commit/f2febdd756723a5835f98ca8cd749d51875f8bcc tdf#155799: sc: fix input formula reference It will be available in 24.2.0. The patch should be included in the daily builds available at https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: https://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Henry Castro committed a patch related to this issue. It has been pushed to "master": https://git.libreoffice.org/core/commit/964fe83ec0ce36dab611c7e1cf22af7c8cce5860 tdf#155799: lok: sc: fix extend reference mark It will be available in 24.2.0. The patch should be included in the daily builds available at https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: https://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.