How can the result of a formula copying the content of a cell to another cell be unlinked from the source cell?

Question

Level 1

22 points

How can the result of a formula copying the content of a cell to another cell be unlinked from the source cell?

Numbers on iMac under Maverick.

For each sheet in a spreadsheet (Numbers file) I have provided a corresponding sheet (to replace it). In the new sheet I want to insert a formula referencing the corresponding cell in the original sheet. I want to be able to delete the original sheet without losing the result in the new sheet. The link has to be broken.

If the formula referencing the cells in the original sheet returns the resultant data in place of the formula, this will enable me to use this method to work through a largish file (and others like it) with comparative ease. In Excel this would be done, for example, by using Paste Value only (an option in Paste Special). In Numbers, I think it can be done for an entire row by using Insert>Copied Rows (and likewise for a whole column), by choosing Values as opposed to formulas (because that action generates new rows and columns and doesn't overwrite existing data).

Is it something to do with generating a more complex formula, or pasting a style using the Format menu? Or the Edit>Paste and Match Style?

It's eluding me.

Posted on Jul 10, 2014 10:34 PM

Reply

Answer 1

Best reply

Yellowbox

Level 7

20,353 points

Jul 10, 2014 11:18 PM in response to peterwhi

Hi Peter,

Copy the cells then Edit > Paste Formula Results.

Regards,

Ian.

Reply

Answer 2

peterwhi Author

Level 1

22 points

Jul 11, 2014 2:35 AM in response to Yellowbox

Thank you, Ian. That sub-menu is what eluded me all right. It certainly seems to me to be one way I can do this: using the Paste Formula Results sub-menu under Edit. But before I copy the cells as you say, I will have to take several prior steps.

In the new sheet I have to select the top left cell of the subject range and there enter "=". Then, in the original sheet, I must select the corresponding cell. Returning immediately to the new sheet, I need to drag the cell right to extend the formula to two adjoining two cells. Now I need to drag those three cells to extend the formula to the bottom of my table (quite a number of rows, but the number matches that of the original sheet). Only at this point am I in a position to apply your suggested method: that is, copy what's already selected and use Shift-Command-V to paste the formula's result throughout the range.

I feel sure that will do it. But if anyone has any further suggestion as to how this could be done less cumbersomely, I would be very happy to hear it before I embark on the task ahead - because that will have to be repeated for another 24 new sheets in a couple of files. Quite a lot of work.

So, whilst you've helped me materially, a question still remains as to whether some other method might be possible?

Regards,

Peter White

Reply

Answer 3

Yellowbox

Level 7

20,353 points

Jul 11, 2014 3:18 AM in response to peterwhi

Hi Peter,

When you have created a formula in the top left cell of the new table, copy that cell then shift-click on the bottom right cell to select the range. Use the menu item

Table > Autofill Cells > Autofill Right.

Repeat with Autofill Down.

Regards,

Ian.

Reply

Answer 4

peterwhi Author

Level 1

22 points

Jul 11, 2014 4:33 PM in response to Yellowbox

That's brilliant. Thanks a million, Ian.

Reply