How to copy formulas to new workbook without referencing original source

**TPS** · 09-11-2013, 08:16 PM

I want to copy a set of formulas I wrote for one workbook and use them in another workbook that is unrelated. But the copy references the original source file. How can I copy just the formulas as they are to use in a new set of calculations? There are about 30 separate formulas I need to replicate. Using the paste formula function does not seem to help.

Example:

Original =SUMIF(Data!$N$6:$N$602,"Urban Fashion 2",Data!$F$6:$F$235)
Copy: =SUMIF('[Sales NEW Analysis 5-6-13 to 23-7-13.xlsb]Data'!$N$6:$N$602,"Urban Fashion 2",'[Sales NEW Analysis 5-6-13 to 23-7-13.xlsb]Data'!$F$6:$F$235)

Thx

**alansidman** · 09-11-2013, 08:25 PM

I would insert an apostrophe (') in front of each formula to convert the data to text. Now copy and paste to the new workbook. Now in the new workbook, remove the apostrophe. If this is something you will be doing regularly, you may wish to save a file with the apostrophes in a template file. Additionally, you may want to automate with some VBA.

**FDibbins** · 09-11-2013, 08:33 PM

The approach I take with this situation, is to copy the formula from the formula bar, then paste it to the formula bar in the new W/B, then just enter

**TPS** · 09-11-2013, 08:47 PM

Thanks guys for the tips. Although I had hoped there was a simple answer, like a shift key or some such. There are too many formulas to treat one at a time.

A bit of lateral thought solved it and here's a trick. Copy the address of the original source that appears in the formula and past into Find and Replace then leave the Replace With empty. It then replaces the address with nothing. Vola!

Should have though of that earlier.

Thanks for your help anyway.