How to copy an area in Calc

[ZASKE_Martin]

In our office, we are using lots of forms, which I build in Calc. Where money is passing between people, I make the form divided and mirror the top at the bottom with a few tweaks (“person giving” versus “person receiving”, etc.)

So far I copy the upper area by hand and past-special all the elements as links. So when I have to edit my form (for a new year or for another context) then the bottom-half will automatically show my edits.

Here is my problem: When I copy and paste-special, even opting for “all”, my special formatting is lost. I believe it could be called “direct formatting”. Row-height, special formatting in cells where I bold certain words, fonts, all gets lost. I know about clone-formatting, but it takes ages, to clone the formatting of many elements of a complex form.

Is there a way to efficiently “clone” an entire area of a Calc-sheet (content plus all formatting) to some other area on the same sheet and still have it so, that I can edit, where I need to tweak?

Update: I keep searching the web and it brought me back to this forum. Here is a similar need and I still hope for an elegant solution, which I can also explain to the team.
https://ask.libreoffice.org/t/calc-copy-a-portion-of-a-sheet-to-another-sheet/66938?u=zaske_martin

[Villeroy]

Alternative approach:

• Select the range in question.
• Call Tools>Scenarios… and enter a name like a sheet name without characters that may be in conflict with formula syntax.

This creates a data copy on a hidden scenario sheet. This results in one formatted range on one sheet with row heights and everything where you can switch between different data sets by choosing a scenario from a list box on sheet or from the navigator window. In the navigator window, you can also edit the properties of existing scenarios.
You can address any scenario’s data with $Scenario_Name.$A$1:F$99 because it is just a special hidden sheet.
Removing cells/rows/columns in a scenario range applies to all underlying scenarios.
If your scenario range is on a protected sheet and the scenario is protected too, you have a read-only scenario.
The “copy back” option (default = on) writes back your modified data when you switch to another scenario. A scenario where this option is turned off can serve as a scenario template: Choose a non-copy-back scenario, edit data, create a new scenario with the modified data. The template remains unchanged.

Sample file with formulas in scenarios and a single chart displaying data from different scenarios (not quite the same as you would do with “forms”): http://user.services.openoffice.org/en/forum/download/file.php?id=3004

[ZASKE_Martin]

Thank you for taking time to answer and for the example. I have managed to make such a scenario from the top-half of a new form. Now I have not found any way to copy or display this scenario into the bottom half of my form. You wrote how to reference it and I found a management window in the Navigator. Still when I just reference with something like =$Scenario_Name.$A$1:F$99 I get a =VALUE error.

I use references myself when I create our forms. I get all the content to display fine but I have to manually correct the formatting, especially any direct formatting. So we need to keep this objective in mind.

I still believe that you believe the scenario could be used to clone an entire area of a sheet. Please just show me a practible way to display any scenario in a location offset from its source-location.

[ZASKE_Martin]

Notes to myself, until a full solution will be found:

I see two main options and both have pros and cons:

1. When I copy and paste-special, and I do not use the option “link” then everything gets copied, the content and also the formatting and even the direct-formatting, like marking certain words in bold. But this way I lose the magic of having any later editing applied to the copied-part of my form.

2. When I copy and paste-special with the option “link”, then almost everything gets copied and properly linked. The only aspect missing is direct-formatting.

So depending on the use for any form, I need to consider whether there is a lot of direct-formatting needed, how similar the cloned-part is to the master-part and whether there is “danger” of many edits at later times:

When a form is complex in design but the cloned-part is very similar to the master-part, use option 1. and just re-copy-and-paste after each edit in the master-part.

When a form needs changing often, and there is plenty work in setting up the clone, then opt for the link-feature and try to keep direct-formatting to a minimum; at least in the areas that will get the edits.