GNOME Bugzilla – Bug 428390
References are not transposed when paste-transposed is used
Last modified: 2018-05-22 13:21:18 UTC
Please describe the problem: Normally I copy and paste a group of cells, references within the group are preserved. However, when I paste-special transposed, the references are broken: it looks like they point to the cell that they would have pointed to if it had not been a transposed paste. Steps to reproduce: Create this sheet, starting at A1: =1 =A1+1 =A1/2 Select those four cells. Copy. Click in C1. Paste special. Transpose. OK. Observe results. Actual results: The results in cells C1 to D2 are =1 =#REF!/2 =B2+1 Expected results: Cells C1 to D2 should contain: =1 =C1/2 =C1+1 Does this happen every time? Yes Other information: I have never looked at the Gnumeric internals, but I presume that the paste code has some reference fixup stuff that does for each cell in selection if cell contains a formula for each reference in forumla if target of reference is in copied region (dx,dy) = displacement from this cell before copy to target before copy new target = this cell after paste + (dx,dy) To make this work correctly with transposition, it's only necessary to add if transposing (dx,dy) = (dy,dx) after computing (dx,dy) above.
Status should be confirmed - I am also having this issue in v1.10.16 (windows) copy-paste special-transponsing cells that reference other cells still mangles things.
Still present in last Windows binary 1.12.17. This problem is now over 7 years old, yet appears to require only a minor code change to fix. There is no work around other than opening the sheet in another spreadsheet application to do the transposing. Perhaps it should be given a higher importance?
This is a feature waiting for someone with the time and skills to implement. I doubt it is particularly hard. Quite clearly it is not a feature missed by the main developers.
In fact, all Paste Special Region Operations have this inability to properly offset cell references: Transpose, Flip Horizontal and Flip Vertical. Still, it appears to be a relatively trivial fix: if a cell addresses another cell within the selection being pasted, calculate the correct row offset, or column offset or both according to the dimensions of the selected cells as well as the relative offset from its original location. Values and references are both transposed properly in Excel (and I personally use this operation frequently). Only values are handled properly by gnumeric, effectively rendering all three Region operations useless. It appears the original open source effort failed to correctly code cell reference offsets for region operations. Regrettably, this little oversight is still not fixed and has now propagated into, quite possibly, all branches and derivative products. The OP has done a great job of providing pseudo-code describing how to properly transpose cell references. Given this problem is larger that originally reported, could we at least get the Status escalated and the Importance re-evaluated?
> Importance re-evaluated? I, for my part, did that in comment 3. Andreas got the same bug updates as me and can chime in if he wants to and that completes the list of regular contributors to this part of the code. We're not exactly overstaffed. It will get done eventually. Faster if someone new steps in. And for the record, the pseudo-code is missing some nasty cases having to do with ranges that are partly being transposed and partly not. I am curious what this is used for, btw., not having ever used it myself for anything but simple values.
> I am curious what this is used for In my case, I had created a largish spreadsheet with something like months as the columns and products as the row names, and then decided that it would work better with time advancing down the screen rather than across it. Gnumeric transposed my data, but messed up all the formulas.
Gee, I didn't know the developer base was that small. I, too, currently have a large sheet, loaded with formulas in which I want to transpose or reverse certain rows and columns. Unfortunately, I still have to rely on Excel, which is best run in it's native O/S. I use spreadsheets as a tool for data structure analysis and relationship discovery - a visual, interactive environment for program design. I like the spreadsheet paradigm for exploring complex problems where the structure of the intermediate calculations may need to be reorganized while working towards a solution. Often this involves parameter generation (i.e. formulas) in both vertical and horizontal directions, which may need to be swapped due to sheet size limitations or visual compactness. I'm frequently reaching row and column limits and often experience Gnumeric crashes due to failed memory allocation attempt. It would be great to have these region operations fully functional and to have Gnumeric gracefully recover from large spreadsheet paste operations which could not complete. My programming skills are rusty and from obsolete languages, but if you direct me to the specific source file(s) which contain the region operations, I could take a look to see if I can offer any assistance.
-- GitLab Migration Automatic Message -- This bug has been migrated to GNOME's GitLab instance and has been closed from further activity. You can subscribe and participate further through the new bug through this link to our GitLab instance: https://gitlab.gnome.org/GNOME/gnumeric/issues/77.