MrExcel Publishing
Your One Stop for Excel Tips & Solutions

copying formulas


Posted by david on November 16, 2001 6:53 AM

A formula in a workbook refers to a cell range in another sheet of the same workbook. If you copy and paste that formula to another new workbook that contains the same sheet names, the formula in the new workbook refers back to the sheets on the original book that you copied from. Is there a way to modify the copy/paste process so the formula in the new book doesn't go back to the old book?


Posted by Mark W. on November 16, 2001 7:08 AM

> ...Is there a way to modify the copy/paste
> process.

No. But you can change the formula using the
Edit | Replace... menu command. Suppose
you pasted the formula, =[Book2]Sheet1!$A$1
from Book1 into Book3. Using the Replace
command just enter "[Book2]" into the 'Find
what' field, "[Book3]" into the 'Replace with'
field, and press [ Replace All ].

Posted by david on November 16, 2001 8:01 AM

Thanks Mark... The Replace idea is a lot better than manually editing each copied formula. You have answered several other questions I've had in the past and I really appreciate your help - this is a great site!