Problem with Moving Worksheet to Another Workbook

[samsilverman]

Using Excel 2007.

I have a workbook that serves as a template for clients we service. Say it's called TEMPLATE.XLSM. Once it is edited for the client, it is renamed, say...CLIENT1.XLSM. I have a new worksheet I've been developing that I want to move into about 150 client workbooks.

So, I've taken the TEMPLATE and have created a new worksheet in it. The worksheet has formulas that access lists and data from other worksheets within the TEMPLATE workbook. Of course, when I move the new worksheet into an existing CLIENT workbook, the formulas reference back to the TEMPLATE workbook like an absolute formula. For example, in the TEMPLATE workbook, the formula for the new worksheet might be:

=IF(ACCOUNT!$C$4="","",+ACCOUNT!$C$4)

But, when I move the new worksheet into an existing CLIENT workbook, it changes to:

=IF('[TEMPLATE.xlsm]ACCOUNT'!$C$4="","",+'[TEMPLATE.xlsm]ACCOUNT'!$C$4)

This happens for all the formulas for any new worksheet I create. I can edit manually, but would rather not have to as there are several technicians interacting with the CLIENT workbooks, making the likelihood of error too high.

Is there a way to just retain the original formula referencing when moving worksheets from workbook to workbook?

Thanks for any help.