# How to paste only formulas in Calc without over writing non-formula cells [closed]

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.

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.

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.

The karma problem should be solved.

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

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

DATAorFORMULAEexclusively(with the exception of a group of formula-rows on top of data columns). Thus I hadn't much to worry about your problem.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 ...

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.