Copy & Paste Formula

Posted by Tom McCabe on September 27, 2001 7:37 AM

Can anyone help me with copying formulae to a new spreadsheet without having the formula link back to the original spreadsheet?
Help would be appreciated.
Thanks
Tom

Posted by Barrie Davidson on September 27, 2001 8:03 AM

Where do you want the formula to link to?

BarrieBarrie Davidson

Posted by Eric on September 27, 2001 8:41 AM

copy the info from the formula bar instead of the cell

select the cell containing the formula, then either hit f2 or click in the formula bar (the box at the top of the sheet that displays the formula rather than the results, of the cell you've selected). Then highlight the formula, copy it (Ctrl-C), hit escape ONE time, go to the other sheet, select the cell you want to put the formula in, and paste (Ctrl-V).

Posted by Tom McCabe on September 27, 2001 9:47 AM

I don't want any link at all. Only the formula is to be copied.

Posted by Barrie Davidson on September 27, 2001 9:54 AM

Eric's suggestion will work if the formulas you want to copy are all the same, just different address references. If the formulas are different, here is what I do in that situation.

Copy and paste the formula. I then do a search and replace, using the file name to search for and replacing this with "". For example, if I copy a formula:
=Sheet2!A1
to a new workbook, the formula in the new workbook would be:
=[Book2]Sheet2!A1
I would then search for [Book2] and replace this with "". In effect, deleting the reference to Book2.

Does this help you out.

BarrieBarrie Davidson

Posted by Tom McCabe on September 27, 2001 11:20 AM

Thanks to Barrie & Eric