Bug 30456 - UI: Enhance Merge Cell Dialog with options"shift content", "keep content in covered cell", "empty covered cell"
Summary: UI: Enhance Merge Cell Dialog with options"shift content", "keep content in c...
Status: RESOLVED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
Inherited From OOo
Hardware: All All
: low enhancement
Assignee: Laurent BP
URL:
Whiteboard: target:5.3.0
Keywords:
: 95338 103122 (view as bug list)
Depends on:
Blocks: Calc-Merge-Split
  Show dependency treegraph
 
Reported: 2010-09-29 04:07 UTC by steffakasid
Modified: 2017-06-03 18:46 UTC (History)
9 users (show)

See Also:
Crash report or crash signature:


Attachments
Example given (7.39 KB, application/vnd.oasis.opendocument.spreadsheet)
2010-09-29 04:08 UTC, steffakasid
Details

Note You need to log in before you can comment on or make changes to this bug.
Description steffakasid 2010-09-29 04:07:34 UTC
I found out, that the SUM() function will use values in merged cells which are not visible.

In example when I merge cell A1 with A2. Where A1 has a value of '9' which will be visible after the merge and A2 has a value of '8' which will be "deleted" after the merge. Maybe I've also cell A3 (value is '10') merged with A4 (value is '1') and I make the SUM function like SUM(A1:A4) the result of this SUM() is '28'. So even the invisible "deleted" values are used for the calculation of the SUM() function.

See example in attachment.
Comment 1 steffakasid 2010-09-29 04:08:27 UTC
Created attachment 39043 [details]
Example given
Comment 2 Sebastian@SSpaeth.de 2010-09-29 06:47:04 UTC
Just FYI: There is an openoffice.org bug describing the same thing at 
http://www.openoffice.org/issues/show_bug.cgi?id=78878 (no action since Feb 2008)

and related http://www.openoffice.org/issues/show_bug.cgi?id=105800 (although this involves linebreaks and merged cells)

Comment from the "upstream" bug is: 
"the cells under the merged ones contain data and this data can be used in
calculations, therefore the SUM function works as expected. But is this the desired behavior ? As this is not a defect but an enhancement
request, user experience has to decide how to proceed."
Comment 3 Kohei Yoshida 2010-09-29 07:13:16 UTC
The current behavior indeed causes an interop issue with other spreadsheet apps, not to mention not very intuitive.  I'd say let's change it.
Comment 4 Kohei Yoshida 2010-09-29 07:15:50 UTC
Taking ownership of this.
Comment 5 Kohei Yoshida 2010-09-29 07:23:26 UTC
Calc internally represents a merged cell via cell attribute; the top-left cell has a "merged" flag while the rest of the cells have an "overlapped" flag.  Other than that, a merged cell is internally represented as a set of individual cells with the aforementioned special flags.

With that in mind, what we need to do is when merging cells, those cells that would be overlapped need to get emptied upon merging.  That way functions like SUM should behave more intuitively.
Comment 6 steffakasid 2010-09-29 07:25:05 UTC
Total agree I was a little bit confused about this behavior and it took some
time to find the failure in my sheet ;) So for me it would be nice when this
will be changed.

Regards
Steffen
Comment 7 Sebastian@SSpaeth.de 2010-09-29 07:31:14 UTC
Yes, that will help SUM(), but what about COUNT(IS_EMPTY() or similar? will they count the merged empty cells?

(The formula is wrong but you get the idea)
Comment 8 Kohei Yoshida 2010-09-29 13:00:59 UTC
(In reply to comment #7)
> Yes, that will help SUM(), but what about COUNT(IS_EMPTY() or similar? will
> they count the merged empty cells?
> 
> (The formula is wrong but you get the idea)

Hmm.  That's sort of a gray area, but I would say in cases where the overlapped cells are referenced directly (i.e. A2 is referenced when A1:A2 are merged), they should behave like empty cells.
Comment 9 Rainer Bielefeld Retired 2010-11-23 09:07:49 UTC
All Platforms, all OS!
Comment 10 Kohei Yoshida 2011-01-04 16:12:22 UTC
Looks like the next significant release will be 3.4, not 3.3.1.  Resetting the target accordingly.
Comment 11 Regina Henschel 2011-01-06 14:39:41 UTC
I find no rule in ODF spec how functions should deal with "covered-table-cell". My personal suggestion would be, that all functions but SUBTOTAL should consider the content of a "covered-table-cell" same as a normal "table-cell". But SUBTOTAL should be extended in ODF in the way, that covered cells can be excluded as it is now for filtered cells or for collapsed cells. But that has to be discussed before writing a request to OASIS.

I disagree, that the covered cells should be emptied on merging. There are documents which use the feature that values in covered cells can be examined. For example the calendars from Bernhard Gottwald, http://www.welter.mynetcologne.de/kalender.html or http://live.prooo-box.org/de/templates/de/index.html "Kalender mit Mondphasen"
Comment 12 Regina Henschel 2011-01-07 06:07:41 UTC
I have tested Excel and Gnumeric. They too consider the content of the covered cells, when they get a document, that has content in that covered cell. They behave the same as Calc in this regard. The difference is, that their UI does not allow to enter content in that covered cell.
I do not like to mimic this UI-as I already wrote. But we have already a dialog, if the cell, which will be covered, is not empty. What about changing our dialog in that way, that it offers three items: "shift content", "keep content in covered cell", "empty covered cell"?
Comment 13 m.a.riosv 2011-10-15 08:38:09 UTC
Great idea Regina.
Comment 14 m.a.riosv 2011-10-15 08:41:20 UTC
I think must be change to enchancement.
Comment 15 Kohei Yoshida 2011-11-10 12:45:09 UTC
It's out of my hands for now.
Comment 16 Rainer Bielefeld Retired 2012-06-17 05:02:15 UTC
Currently it's not common sense that that should be done, but IMHO a good idea.
Comment 17 QA Administrators 2014-10-23 17:32:14 UTC Comment hidden (obsolete)
Comment 18 steffakasid 2014-10-23 20:03:39 UTC
Still not working on LibreOffice 4.3.2.2 on Mac OSX.
Comment 19 Joel Madero 2014-12-15 01:02:50 UTC
Hey Steffakasid -

Thanks for the update.

I've done further tests and this is definitely inherited from OOo and was probably designed this way to be interoperable with other spreadsheets out there.


That being said - just going to update the version field (version is oldest version where we can confirm the issue).

As this is a valid enhancement - it'll take a volunteer to decide to tackle this. Probably once a year there will be a ping to verify that the issue is still present.
Comment 20 WoSch 2015-11-04 10:48:01 UTC
Bug is still present in LibO 5.0.2.2 (with Win 7 prof 64 bit) 
as you can see by report #95338. 
 
In my opinion cells, wich are covered, should not be used in function/formula. Result of LibO-calculation must be the same as by calculation in mind seein some cells an some other not. And results should be the same when using MS Excel ore other Soffice-software. 
 
I don't see any reason to keep LibO's actual behaviour. Is there any example justifying this Point?

(In reply to QA Administrators from comment #17)
> Please read this message in its entirety before responding.
> 
...
> 
> If you have time please do the following:
> 1) Test to see if the bug is still present on a currently supported version
> of LibreOffice (preferably 4.2 or newer).
> 2) If it is present please leave a comment telling us what version of
> LibreOffice and your operating system.
Comment 21 Regina Henschel 2015-11-04 14:08:47 UTC
(In reply to WoSch from comment #20)
> Bug is still present in LibO 5.0.2.2 (with Win 7 prof 64 bit) 
> as you can see by report #95338.

It is not a bug.
 
>  
> In my opinion cells, wich are covered, should not be used in
> function/formula. Result of LibO-calculation must be the same as by
> calculation in mind seein some cells an some other not. And results should
> be the same when using MS Excel ore other Soffice-software.

No one forces you to use that feature.
 
>  
> I don't see any reason to keep LibO's actual behaviour. Is there any example
> justifying this Point?

See the mentioned document in my comment #11.

LibreOffice is no clone of Excel. If Excel does not have an UI for this feature, that is no reason to reduce LibreOffices capabilities.
Comment 22 WoSch 2015-11-05 08:14:54 UTC
Hello Regina,

(In reply to Regina Henschel from comment #21)
> (In reply to WoSch from comment #20)
> > Bug is still present in LibO 5.0.2.2 (with Win 7 prof 64 bit) 
> > as you can see by report #95338.
> 
> It is not a bug.
>  
It's a bug! I have to contradict. Not because of MS Excels behaviour. 

But because of experience of life. In school we've had folding boards - starting calculations on the one behind and going on with interim results 
(like SUBTOTAL) on the front one. Nowadays they might use Whiteboards, copyboards or other electronic devices - but the strategiy is the same. 
You will transfer interim results to a new page and contionue calculating without using terms of the deleted board again. 

It's a question of well-known usabillity!
> >  
> > In my opinion cells, wich are covered, should not be used in
> > function/formula. Result of LibO-calculation must be the same as by
> > calculation in mind seein some cells an some other not. And results should
> > be the same when using MS Excel ore other Soffice-software.
> 
> No one forces you to use that feature.
> 
Shure am I forced to use merging cells and/or usig properly working formula/function!
> >  
> > I don't see any reason to keep LibO's actual behaviour. Is there any example
> > justifying this Point?
> 
> See the mentioned document in my comment #11.

Please give actual links - the published do not work. I'm very interestet in seeing, how actual behaviour is deadly neccessary. I have an idea using this, but i can create same results by simple using =A1&A2&A3 or CONCATENATE or something like that. 
 
Only if the actual behaviour of merging cells is meaningfull and important, LibO Calc needs a second Dialog "clear covered cells?".
Comment 23 Laurent BP 2016-07-19 15:50:58 UTC
*** Bug 95338 has been marked as a duplicate of this bug. ***
Comment 25 Commit Notification 2016-08-16 19:22:22 UTC
Laurent Balland-Poirier committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=0da121bc6702b8ae1859708c7e5532f7b97e4c2a

tdf#30456 Enable to empty or not merged cells

It will be available in 5.3.0.

The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.
Comment 26 Commit Notification 2016-08-18 19:56:29 UTC
Laurent Balland-Poirier committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/help/commit/?id=ea4100df0c087e2439ae438cfa7a3d35efb6dd27

tdf#30456 Add help to Merge Cells dialog
Comment 27 Phil 2017-05-04 08:18:57 UTC
*** Bug 103122 has been marked as a duplicate of this bug. ***