How to paste only formulas in Calc without over writing non-formula cells

[5auli]

I want to be able to copy and paste only formulas in case when I have e.g. several similar columns, rows or sheets containing different values of data. By formula here I mean something beginning with “=” and by values of data I mean numbers or text.

This situation may arise e.g. when analyzing data of comparable items like profits of different investment alternatives. After entering the formulas (or equations) for profit calculation and data for various items to different columns/rows/sheets one may notice that some of the formulas are incorrect. It would be convenient to correct the formulas only to one column/row/sheet and then copy and paste the formulas without over writing the data.

A simple example. Let’s assume I have copied two cells, A1 and A2. A1 has a formula ‘=A2+1’ and A2 has a number 2’’.
By pasting I want to replace the old formula of cell B1 ‘=B2+3’ with the formula of cell A1 (with relative referencing) without replacing the content of cell B2 which has a number ‘4’. If I use the “‘Paste Only’ > Formula” what happens is that the content of B1 will be changed as intended to ‘’=B2+1’ but the cell B2 value will be deleted.

[Lupp]

Sorry, I had neither the time nor the patience to study your own solution in detail. But …

If your apparatus of formulae is not too complicated you may assign a name to every formula, at least to everyone that might undergo further development. Editing such a formula later with the help of the ‘Insert’ > ‘Names’ > ‘Manage’ tool this will affect all the occurrences at once.

[5auli]

Thanks, I have to think about your proposal.
Concerning my own solution (Oct 9th 2014), I am pretty confident that the principle is correct even quite complex. However, I did notice I made a minor mistake when I wrote it here. I think I will wait that I get enough karma to be able to add the screenshots so that it is more readable. Meantime I will study if I can simplify and reduce the steps required.

[Lupp]

The karma problem should be solved.

[5auli]

Thanks Lupp, I rewrote my own solution with screenshots. I hope someone will find an easier way to achieve the same result.

[Lupp]

What are your urgent reasons to mix up data and formulae in the same column? If I had a somewhat database like task for Calc i mostly tried to have columns (or even complete sheets) containing DATA or FORMULAE exclusively (with the exception of a group of formula-rows on top of data columns). Thus I hadn’t much to worry about your problem.

[5auli]

In principle I agree. In ideal world at least. The need comes from the way how I often do analysis. In the beginning I believe I’m doing something small and simple. Then I add some parameters and formulas, then I add some more items to be analysed. After a while I decide to move each item to separate sheet believing the analysis model formulas are correct. And finally I notice that the formulas do need some tweaking - in each sheet. I know this is not ideal, but …

[Lupp]

OK I well know the phenomenon you described. It seems to be ubiquitous, not only when using software. There are yet a few countermeasures as far as Calc is concerned. In most cases you can double the number of used columns and you will already have a sufficient separation of the data from the formula apparatus.

[ROSt53]

I would do:

• Select A1 and A2 and ctrl+c
• click into B1
• right click B1
• select “Paste Special”
• select Text, Numbers, Formula

[5auli]

Thanks for your answer but that is not what I asked. I try to be more clear with my question. Instead of editing my original question I write it as a new message.

[5auli]

---

Note. If you want to display the formulas in the cells, like in the “images” above, proceed as follows:
Choose Tools - Options - LibreOffice Calc - View.
In the Display area mark the Formulas box. Click OK.

The following is my own answer

In left the normal calc view,in right the formula view.

The challenge is to copy only the formulas from the column A to column B without overwriting the content of cells that contain text or numbers. In the example the challenge is to copy only the cell A1 to B1 and save the content of the cell B2.

The first step is to copy to a temporary location the formulas from the cells that contain formulas and values (text or value) from other cells. This is achieved by formula =IF(ISERROR(FORMULA(B1)),B1,FORMULA(A1)) in column C cells (naturally the 1 replaced by the respective row number).

The second step is to convert the cell content to a string that can be edited. This is done by Copy + Paste Special > everything but Formulas. In the view on left it looks like nothing happended but the change is visible on right.

The third step is related to a strange way how calc works. Even the D1 contains something that looks like a formula unfortunately calc recognizes it as a formula only after doing some editing to it. A save way to edit is to replace the equal sign with equal sign. Yes that is in effect no change but calc recognizes the edit. The way to do this is by normal Edit > Find & Replace “=” → “=” to the whole colum D. Note how this time nothing changed on right view.

The fourth step is to correct the relative reference. Instead of the original “A2+1” we should have “B2+1”. This can be easily achieved by copying the column D to column E.

The fifth and final step is to cut and paste the column E to column A. Next we could delete the temporary columns C, D and E.

copypasteformulasonly.ods

[Lupp]

1. Am afraid I did not understand the fifth step. Might you be ready to supply a Calc document containing 6 sheets: Sheet1 containing the first state and the following sheets containing the result of one by one steps?

2. The third step can be done with the help of the ‘Data’ > ‘Text to Column’ tool.

[5auli]

1. Concerning the fifth step, it is done because the challenge was to copy only the formulas from the column A to column B. I will provide the example as a 6 sheet document later. Maybe today. 2) Concerning the third step, thanks, you are right. It can be done with ‘Data’ > ‘Text to Column’ tool. As a matter of fact I find both the ‘Find & Replace’ and the ‘Text to Column’ methods a bit quirky ways to circumvent the calc idiosyncracy.

[Lupp]

I have no access to Excel for about 7 years now. In earlier times I had it at hand for comparison. Excel also required special means to trigger the “recognition” process for cell contents. So did StarOffice.(the common ancestor of the open office suites) I knew since 1997. There was no “calc idiosyncracy”.

[5auli]

I added the calc document at the end my answer. Starting state on first sheet and each step on following sheets.