Copy and paste formula to different workbook

[Skye]

Hello,

I have several formulas I am setting up for 20 spreadsheets (Excel 2003). I need to copy multiple formulas from one spreadsheet to another in a different workbook (I am just going this process once for the 20 spreadsheets and then I will not need to edit them).

When I select the data and then select paste-special (formula) the reference to the previous workbook is added to the formula. I do not need the reference to the workbook and I am currently manually editing each formula to delete the reference to the previous workbook.

For example the formula I want to copy is:
=COUNTIF(Rawdata!M2:M50,"Gordonvale")

when I copy and select paste-special (formulas) into the different workbook the formula is now:
=COUNTIF('[Cairns & Hinterland MDS_Q1.xls]Rawdata'!M2:M50,"Gordonvale")

ie. I am looking for a solutions so the '[Cairns & Hinterland MDS_Q1.xls] is not carried over with the formula.

Thank you for any advice.

Skye

 

[Nimit]

Select or highlight the whole worksheet once you are done with copying the formula over, press Ctrl+H, copy the text "'[Cairns & Hinterland MDS_Q1.xls]" without quotes in the "Find what" option, leave the "Replace with" option blank and click on "Replace All".

 

[Skye]

Thank you!