Description: I want to streamline a sequence of Copy and Paste Special operations down to a single Copy and Paste Special operation by adding a property to each attribute of a cell called "stickiness", so that the designer of a source range doesn't need to communicate to the consumer (the one who controls pasting) the sequence of subranges to copy and the special subset of features to paste. To put it plainly, in my use-case a large range contains sub-ranges having formulas, some which are to be pasted verbatim and some which will be pasted as values computed from lookup tables. I want to do it all at once and I don't want to write a macro to do this. Stickiness is picked up from features of the source range by Copy (by default everything is sticky) and Paste Special Sticky ignores features of the source range which are not sticky. The stickiness property is attached to each cell feature: content (formula/value), font, background colour, numeric format, etc. and cells in the range can individually have different sets of features which have been marked as "not sticky", so that the whole source range can be selected and a single Paste Special Sticky does it all for me. This eliminates a source of error and repetitious manual work at the same time as it eliminates the need for a macro to do the same. When used in a macro, Paste Special Sticky eliminates a lot of code and so potentially a lot of code maintenance. As such this is a productivity enhancement for the spreadsheet designer as well as the spreadsheet user. Steps to Reproduce: Consider your experience as a user. Actual Results: Remember what happened. Expected Results: Use your imagination. Reproducible: Always User Profile Reset: No Additional Info: Enhancement - applies to all new releases.
And maybe check-out https://www.libreoffice.org/get-help/professional-support/ Bit more specific, because the site being kind of overwhelming: Collabora or CIB These are non free options to get certain functions.. those company's are backing LibreOffice. Your wish might be fulfilled here (if passing UX test), but could possible take a couple of years.. 5 pretty normal.. 10-15 not uncommon ... and even longer surely possible. So if the gain (time/comfort) for spreadsheet designer as well as the spreadsheet user being big enough (so the economics), you could consider trying to gather some money for it to make it happen using one of the company's above (professional support)
If I hopefully understand the question, you don't need anything new. On the source spreadsheet give a named range to the source data ranges. On target spreadsheet, go to the where to import one range, then Menu/Sheets/Link to external data, select the source file, enter to get updated Tables/Ranges, select one, and Ok. It imports data, formulas and formats.
Created attachment 168617 [details] Sample ODS source file You need to save modifications in this file and update in the destination file.
Created attachment 168618 [details] Sample destination file
I am a software professional and I would contribute the changes to LO-Calc myself if I didn't have so much work already. I imagine what I'll be doing in 5 years isn't very different from what I'm doing for salary so this feature might appear here earlier than that. But I'm not discouraging anyone from taking on this project before I get around to it, it looks like fun. Thanks for the example. I must apologize - in an effort to generalize the concept of stickiness and simplify my example use case, I didn't describe clearly enough what I am doing. I copy a range from one sheet to another within the same document file, inserting a new row into a table at the destination. A pivot table summarizing that destination range computes values which are used in the source range's calculations. I want to paste from the source range some formulas and some values and some formatting as a template at row insertion time. I could get away with inserting all as values into the destination if the source was the only origin for data at the destination, but it's not. The sequence of copy/pastes is mundane manual work in a few steps, but I wouldn't dream of turning this document over to an unsophisticated user without a macro codifying the source and destinations and the types of pasting to do. The stickiness property described is a UX feature that does away with maintaining macro details for the specifics and the sequence of copy-and-paste-special operations, as it all gets done in one go with the sticky bits in each individual cell's properties directing Paste Special Sticky what to do with the cell at the destination. This proposal is about generalizing the approach so that every user-modifiable property of a cell can become sticky or not, changeable with the cell properties dialog. Use of Paste Special Sticky in a macro would be as simple as using Paste - the macro can ignore the internal structure of the source range. An added note about Paste Special Sticky: stickiness of properties is propagated by Paste, but not by Paste Special Sticky. The latter does not change the stickiness of properties at the destination. I understand that enhancement requests can take a long time if they don't get flagged as GSoC projects. More comment is welcome.
(In reply to Robert Lacroix from comment #5) > I am a software professional and I would contribute the changes to LO-Calc > myself if I didn't have so much work already. Do it yourself is even more appreciated :P. Some info about development: https://wiki.documentfoundation.org/Development IRC DEV channel probably the easiest way to get in contact with experienced LO developers. Warning: There is a pretty steep learning curve. And code is not always at it best. Both only 'second hand' information.. not an developer. Opengrok is can be useful to find things in the code (and look up the history): https://opengrok.libreoffice.org/
Don't see this going to fly. We are restricted to the open document format and cannot bind any property to cells. My take => WF. Plus, regular copy/paste interactions really sounds like a job for functions.
(In reply to Heiko Tietze from comment #7) > Don't see this going to fly. We are restricted to the open document format > and cannot bind any property to cells. My take => WF. > > Plus, regular copy/paste interactions really sounds like a job for functions. Ironically, ODF explictly allows implementation-dependent content. Furthermore, XML inherently allows extensions to a schema; a document processor should preserve original content not defined in the schema. Something to look into in LO. Anyway - something I agree with - a collection of sticky properties (grouped as a style) would be more useful than the general case of every cell having an independent set of sticky properties. It would require the implementation of Paste Special Sticky to look at the style attached to a cell to see what is permissible to copy. Would that fly? 3.1.1 General OpenDocument defines two methods of document representation: •A single XML document. •A collection of files within a package ([ODF1.3-Part-2:Packages]), each of which stores a part of a complete document. ... A package may also contain image files, embedded objects and implementation-dependent files. https://docs.oasis-open.org/office/OpenDocument/v1.3/cs02/part3-schema/OpenDocument-v1.3-cs02-part3-schema.html
(In reply to Robert Lacroix from comment #8) > Anyway - something I agree with - a collection of sticky properties (grouped > as a style) would be more useful than the general case... We do have Table Styles (in Calc applied per Format > AutoFormat Styles... after a range of cells has been selected). You can take the current formatting into a style and apply this later to any other range. Search for Table Style what is missing (a lot) and what is possible (the help should be informative here).
(In reply to Heiko Tietze from comment #9) > (In reply to Robert Lacroix from comment #8) > > Anyway - something I agree with - a collection of sticky properties (grouped > > as a style) would be more useful than the general case... > > We do have Table Styles (in Calc applied per Format > AutoFormat Styles... > after a range of cells has been selected). You can take the current > formatting into a style and apply this later to any other range. Search for > Table Style what is missing (a lot) and what is possible (the help should be > informative here). This really doesn't handle the use-case I was considering, where the designer and the user of the spreadsheet are not the same person. The gist of the proposed feature is that the creator of a source range has some control over what properties are pasted into the destination range. The majority of spreadsheets are built by their users. The opposite case which is poorly represented in LO is more often seen in an enterprise setting, where the person using a spreadsheet isn't the one that created it. Microsoft handles this very well with .NET, allowing the programmer to effectively use a spreadsheet as a user interface for an application.
@Michael There is feature proposal.. the person who is requesting this is a developer himself and even considering building it (comment 5) And thought you might be interested - holding opinion - as it's direct to 'enterprise setting' and Calc. (In reply to Robert Lacroix from comment #10) > (In reply to Heiko Tietze from comment #9) > > (In reply to Robert Lacroix from comment #8) > > > Anyway - something I agree with - a collection of sticky properties (grouped > > > as a style) would be more useful than the general case... > > > > We do have Table Styles (in Calc applied per Format > AutoFormat Styles... > > after a range of cells has been selected). You can take the current > > formatting into a style and apply this later to any other range. Search for > > Table Style what is missing (a lot) and what is possible (the help should be > > informative here). > > This really doesn't handle the use-case I was considering, where the > designer and the user of the spreadsheet are not the same person. The gist > of the proposed feature is that the creator of a source range has some > control over what properties are pasted into the destination range. > > The majority of spreadsheets are built by their users. The opposite case > which is poorly represented in LO is more often seen in an enterprise > setting, where the person using a spreadsheet isn't the one that created it. > Microsoft handles this very well with .NET, allowing the programmer to > effectively use a spreadsheet as a user interface for an application.
What comes in mind is the cell protection. The format defines: 20.246 style:cell-protect The style:cell-protect attribute specifies how a cell is protected. This attribute is only evaluated if the current table is protected. The defined values for the style:cell-protect attribute are: ●formula-hidden: if cell content is a formula, it is not displayed. It can be replaced by changing the cell content. Note: Replacement of cell content includes replacement with another formula or other cell content. ●hidden-and-protected: cell content is not displayed and cannot be edited. If content is a formula, the formula result is not displayed. ●none: formula responsible for cell content is neither hidden nor protected. ●protected: cell content can not be edited. ●protected formula-hidden: cell content can not be edited. If content is a formula, it is not displayed. A formula result is displayed. Doubt this can be done without bending the format and breaking compatibility.