Make sure that any cell references in the formula are absolute (ie have
$ before each column letter and row number).

Copy and paste the cell(s) normally.

Then select all the cells you want to refer to a different workbook and
Edit / Replace / current filename /(with) new file name / Replace All

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup