Paste a formula

geoffL · August 30, 2023, 10:35pm

I want to paste a formula (not the value) into a spreadsheet.
I have tried Paste Special > Formula but it doesn’t work.

EarnestAl · August 30, 2023, 11:51pm

Where are you copying from? If you are on Windows then this might apply,
Bug 124006 - EDITING: copy & paste from AOO to LO or between two LO instances fails for formulas

The formula will copy from same Calc to same Calc, or Excel to Calc.

geoffL · August 31, 2023, 3:31am

I am using Windows 11 (latest version).
I want to copy and paste in the same LO spreadsheet.

geoffL · August 31, 2023, 4:03am

Hello EarnestAI

I have found a workaround,
Insert a Text Box into the spreadsheet and paste the formula.

Thanks
Geoff

robleyd · August 31, 2023, 4:06am

@geoffL Have you tried pasting into the input area rather than the actual cell?

geoffL · August 31, 2023, 4:48am

Hello @robleyd

Yes, I have tried that.

I have found a Workaround:

Copy the fornula (not the value).
Insert a Text Box into the spreadsheet.
Paste into the Text Box.

Thanks for your reply.

erAck · August 31, 2023, 3:52pm

Why even a text box? If you want to paste the literal formula text not to be evaluated then hit ' apostrophe first to start the cell and then paste the formula text after. As EarnestAl said.

geoffL · August 31, 2023, 9:24pm

Hello @erAck
Once again you come up with a good solution. This apostrophe method (also suggested by @EarnestAl) is far better than my amatuer idea.
However, there is one problem.
If you add or delete rows the pasted formula does not automtically adjust.[I have the calculations and the pasted formula in the same spreadsheet.]
I find that using the following method allows you to add or remove rows.
1.Right-click an empty cell and select Format Cells to open a dialog.
2.Select the Numbers tab to display a list.
3.Select Number > General (from the list) then click the OK button.
4. Click the green fx to display the Function Wizard dialog
5.Type ‘for’ into the search box of the Function Wizard to display a list.
6.Select FORMULA and click the Next button.
7. Click the cell displaying the result to make the formula appear in the Result box of the Function Wizard.
8.Click the OK button.
9. The formula should appear in the chosen cell.

EarnestAl · August 31, 2023, 9:26pm

So you should have read my solution from yesterday. I guess doing some research will be helpful in the future though.

LeroyG · August 31, 2023, 10:59pm

If what you need is to see the formula momentarily, you can choose the menu View - Show Formula (Ctrl+´).

EarnestAl · August 31, 2023, 4:40am

I think you meant to say that you want to display the formula. Enter this into the cell where you want to display the formula from another cell, e.g. display the formula in C1, =FORMULA(C1)

Else copy the formula from the formula bar and paste it into a cell formatted as Text. Or with the same copy enter an apostrophe into the cell first and then paste.