User-Agent: Mozilla/5.0 (X11; Ubuntu; Linux x86_64; rv:47.0) Gecko/20100101 Firefox/47.0 Build Identifier: LibreOffice 5.2.1.0.0 If I create a formula in a cell, and I then want it to act in a merged group of cells, the formula is converted to text. Say I want this to be part of a heading across multiple columns. Reproducible: Always Steps to Reproduce: 1. create formula in a cell 2. select 2 or more cells 3. select "Merge Cells" 4. the formula is converted to text Actual Results: formula converted to text Has been doing this in previous beta versions does the same in LO 4.4.3.2 so is not a new issue. work around is to merge cells first, then to paste formula into merged cells, however, this is counter-intuitive, especially for persons who have to use MS Excel a lot Expected Results: Should keep the formula, or there should be an option to do so. not apply relative addressing changes, since this is a move (cut&paste), not copy, if it has to move the formula to the first cell. [Information automatically included from LibreOffice] Locale: en-US Module: SpreadsheetDocument [Information guessed from browser] OS: Linux (All) OS is 64bit: yes Reset User Profile?No
Created attachment 126407 [details] merge cells loses formula cells c4 and c5 contained the same formula before the merge. cells c4-e4 is the result after the merge
Created attachment 126408 [details] ms excel example after the merge cells provided for comparative purposes
(In reply to Elmar from comment #1) > Created attachment 126407 [details] > merge cells loses formula > > cells c4 and c5 contained the same formula before the merge. > cells c4-e4 is the result after the merge Could repro in 5.1.4, if I answered "Yes" to the question of whether to move content of empty cells. In 5.3 it did not ask and there was no problem. With your document, I merged and centered C5-E5. Elmar: could you test with 5.2? 64-bit, KDE Plasma 5 Build ID: 5.1.4.2 Arch Linux build-1 CPU Threads: 8; OS Version: Linux 4.6; UI Render: default; Locale: fi-FI (fi_FI.UTF-8) Arch Linux 64-bit, KDE Plasma 5 Version: 5.3.0.0.alpha0+ Build ID: c1b665fcdacd4141137f1e369527e2c0d94513ae CPU Threads: 8; OS Version: Linux 4.6; UI Render: default; Locale: fi-FI (fi_FI.UTF-8); Calc: group Built on July 26th 2016
Buovjaga: yes, I go the same results. So this is an education issue. Problem: MS is the dominant player and thus many will have used Excel first before coming to Calc. Excel 2013 offers a number of options in the merge, but all of them convert the contents to text. What to do? The text in Calc says: "Should the contents of the hidden cells be moved to the first cell?" Here are some options: "Should the contents of the hidden cells be moved to the first cell and converted to text?" or "Should the contents of the hidden cells be moved to the first cell? Yes: convert to text No: selected cell determines what to display in merged view" You say 5.3 adopts the MS approach. I think there is a case to be made to keep the up-to-Calc 5.2 approach. The reason: have a look at the new example. If I have a formula which includes too much text to display in a cell I can simply merge cells which I don't need to display. This is a far more elegant solution that what is offered by Excel 2013 (which I have shown in the shaded text box.) In Excel, the contents of the merged merged cells is lost.
Created attachment 126515 [details] Proposal for resolving the issue
Afterthought: Interestingly, Excel 2013 quite happily accepts my Calc xls save - when I unmerge the cells, the first option (No) returns it the way it was before I merged. Of course, in the case of the Yes option, the 2nd and 3rd cells are empty as expected.
I think this has been resolved in v6.1