Looks like no one’s replied in a while. To start the conversation again, simply ask a new question.

Copy formula in cell without copying results in that cell

I have a list of cells with formulas; all of them have results in the cells. I'd like to copy this stack of cells to another sheet without having to recreate the formulas, but all I get when I paste, is the numeric result rather than the formula. Is there a way?

Posted on Mar 11, 2018 4:50 PM

Reply
11 replies

Mar 11, 2018 6:01 PM in response to NMC-indy

Odd.


I did these steps:

Selects the cells in column E, then copied.
(The cells contain a formula referencing the values in column D)
User uploaded file

Opened a new Numbers document, and

  1. Selected D1 and Pasted
  2. Selected A1 and Pasted
  3. Clicked in the open canvas and pasted

Results:

User uploaded file

Column D: Formulas are in the cells. All formulas have been recalculated, and the results are displayed.

Column A: Formulas are in the cells, and display a 'bad ref' error message.

New table: Formulas are in the cells, and display a 'bad ref' error message.


All results as expected. The error messages arise from the formulas attempting to reference cells in the column to the left of the column containing the formula. The results in D are correct for the formula used: D2: ISEVEN(C2)


Test done with Numbers 3.6.2 and OS X 10.11.6.


Possibly a Hi Sierra/Numbers 4 issue/


Regards,

Barry

Mar 13, 2018 4:41 PM in response to NMC-indy

You are doing nothing wrong. I tried something more like what you have with a formula that references cells in another sheet. I got the same result as you. From what I can tell, formulas that reference cells outside of the table get turned into values except for those that reference cells in Sheet 1 Table 1. I can copy/paste formulas like =Sheet 1::Table 1::B2 but only if it is Sheet 1::Table 1. Seems pretty limiting.


For one or several formulas, you could copy the text of the formula and paste it into the new document then put an "=" in front of it (but you can't do it the other way around, the simple way, and put the "=" then paste in the formula, it gives REF errors).


For a big document, I think the advice to duplicate the original document is probably easiest.

Mar 13, 2018 2:35 PM in response to NMC-indy

"The column of cells all reference 12 corresponding cells on 12 sheets (an annual expense tracking) and brings them all into a total for the year. I know that when I paste them into the new document, those sheet/cell references are not in place yet. I plan to build out each sheet as the new year progresses."


Since you already have the full document made, why not just duplicate the file, delete the current data, and save the 'empty' document as a Template.


After that it's an annual matter of

  • opening the document from the Template chooser
  • Entering the new date(s)
  • Saving the file for the current year
  • Entering the new data and saving as the year goes on.


Next year, open a new document from the Template Chooser, and carry on.


Regards,

Barry

Mar 11, 2018 5:08 PM in response to NMC-indy

Hi NMC'


Normal behaviour of Numbers when you select a cell or group of cells and Copy is for Numbers to copy both the formula and its most recently calculated result.


When you select a cell and Paste, the formula is pasted into the new cell and is immediately recalculated and the result displayed.


You can choose to paste only the most recently calculated result(s) by selecting the cell(s) and going Edit (menu) > Paste Formula Results.


How are you pasting?

Where are you pasting? (You state you "paste this stack of cells to a new sheet." If this is true, the result should be a new single column Table.)

Have you checked this by clicking one of the pasted cells and looking at the 'contents box' at the bottom left of the document window? Or by clicking twice on the cell to open the Formula Editor and view its contents?


Regards,

Barry

Mar 11, 2018 5:35 PM in response to Barry

Thanks Barry, for taking the time to reply. I am pasting the column of cells into a new Numbers file. The contents field below shows just the results quantity, and not the formula. Same if I double click the cell itself. I try this with just one cell to test and I get the same results. I find if I have a cell with formula and no results in the cell, that I can copy/paste the formula to a new cell, but if there are already results in that cell, I don't bring the formula along.

Mar 13, 2018 5:05 AM in response to Barry

Not a Hi Sierra and Numbers 4 problem. Works fine here.


I copied a range of a table then pasted it into a table in a new document and it recreated it, formulas and all. Did the same, pasting into a new sheet in the original document and got the same result.


I pasted it onto the canvas in a new document and it created a new table from it, formulas and all. I did the same in a new sheet in the original document and got the same result.


I pasted it into an Excel worksheet and got just the numbers.

Mar 13, 2018 5:54 AM in response to Badunit

Pretty sure. The column of cells all reference 12 corresponding cells on 12 sheets (an annual expense tracking) and brings them all into a total for the year. I know that when I paste them into the new document, those sheet/cell references are not in place yet. I plan to build out each sheet as the new year progresses. Maybe that's the problem. But I expect my results to have empty ref. errors, but I'm just getting the numbers rather than the formulas. Maybe I'll try to build out 2018 months and see if I have any different results.

Copy formula in cell without copying results in that cell

Welcome to Apple Support Community
A forum where Apple customers help each other with their products. Get started with your Apple ID.