https://bugs.documentfoundation.org/show_bug.cgi?id=172349
--- Comment #7 from Jörn Kaster <[email protected]> --- (In reply to ady from comment #6) > Basically what you say is that you don't want to use absolute references. > > If the merged range is A10:A15, your dependent cells / formulas should not > use "A10" as reference, but "A$10", so when copying the formula down the > resulting formulas will still point to the only real value. > > If the merged range is A10:E10, then use "$A10" as reference in dependent > formulas. > > Similarly, if the merged range is A10:E15, then the dependent formulas > should use "$A$10". > > I'll ask again... Is there any spreadsheet tool that implements the optional > behavior that you are requesting (i.e. automatically fill the "rectangular > tail" of the merged area with a formula that points to the "home" cell of > the merged area, if the whole "tail" has no content when merging)? The word basically isn't right here. First of all 1. I'm not unhappy with the featureset that calc serves, i love libreoffice or moreover open source for these dozens of features it serves. 2. I don't know how other spreadsheet tools work or what featureset they have. I just talk about calc because that is the program i use and the behaviour is problematic in my opinion. 3. Also i think my use case is not very specific to me and i think it would arise very often e.g. in company use. I talk about multiple things. 1. Difference between behaviour and ui. The ui handle a merged cell as one cell. i can't select (with mouse or with arrow keys during formula creation) any individual cell in a merged cell area. So it could arise the situation where i have merged cell area (A1:A3) and write formula in B2. The formula would look like =A1*10 (created with mouse or arrow keys in cell B2). when i then drag the formula down and up for example to the range B1:B4, then there will be multiple results A: Cell B1 shows Reference error B: Cell B2 shows correct result C: Cell B3 can show different things. C1: A result that derives from previous value (before merging) in cell A2 (instead of A3, here is the dragging functionality broken in my opinion) that is not shown in ui, C2: or a empty value is used and the behaviour is dependent to the functions used in formula that is dragged. This behaviour is sometimes very problematic because when formula (in B3) is shown with 'F2' key, then calc will show the referenced cell A2 (correct) as bordered cell but with a value that is assigned to cell A1 (wrong value, because of vertical central view of value of merged cell area) C3: Other things i don't have found or know that it can arrive. D: Cell B4 shows wrong value because of wrong reference to A3 (result from broken drag functionality) 2. Problematic behaviour when i have written formula in one cell (e.g. B1) that is referencing to a column where a mix of single cells (A1,A2,A8,A9,A10) and merged cell area (e.g. A3:A7) is referenced and want to drag the formula down. Here the before mentioned break of the drag function is active 3. For the user it is not visible in ui why calc will behave in situations mentioned before like it does. During writing this post i think i have lost some thoughts. But i think the main case is clear. Could it be helpful to talk during a videocall on this topic? If yes, just reach out to me. Greetings -- You are receiving this mail because: You are the assignee for the bug.
