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.

[Regina]

So the task is to get a linked copy including formats. Here a crazy idea for that:
CopyByPseudoSort.ods (22.1 KB)

Make sure the source range has a column (or row) with blank cells. Define for the source range a database range (menu Data > Define Range) that includes these blank cells. In the Options of the Define Range dialog deselect ‘Contains column labels’ and select ‘Keep formatting’. OK. Select this range (menu Data > Select Range).

Now we do a pseudo sorting: Menu Data > Sort… In that dialog set the empty column (or row) as Sort Key. Select the direction, for a column you need Top-to-Bottom, for a row Left-to-Right. Then go to the Options page of the dialog. Select the option Copy sort results to: and enter the address of the top-left cell of the desired range of the copy. Make sure that the option Include formats is selected. OK. You should get a copy including the formats.

If blank cells in the source do not work for you, use a series 1, 2, 3, … in those cells and format the font color same as background color to make them invisible.

When you now change something in the source range, set cell cursor in the source range and use menu Data > Refresh Range.

This works, because sorting is a push action that pushes the sort result into the target area including formats. Only that here nothing is changed in the order because of the special column (or row).

[sokol92]

By the way, Excel has a Camera tool. Perhaps LibreOffice will have one in the future.

[mariosv]

It can be done in Calc, pasting using the down arrow at the right of the paste icon.

[Villeroy]

The Excel camera updates with its source range. Calc inserts a snapshot.

[ZASKE_Martin]

I am on holiday, so will only test this in December when my inbox will be cleaned up. But I gave you a heart for this “crazy idea”. Nice lateral thinking, since you know that some feature with push action is needed.

[ZASKE_Martin]

We do not change our forms daily. Certainly each year and sometimes during the year, as the workflow needs to embrace changes.

So the idea of quickly taking a snapshot (including the formatting hopefully) and pasting it is interesting to know about. Thank you.

So far, it is helpful to the team to know exactly who is getting which portion of the form, so we are not using exact clones, like photos or snapshots. But still useful and will go into my brain (I hope).

[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.