Copy Formula That References Another Sheet

[JR573PUTT]

How can I copy a formula that references another sheet to a new file"

file name: sales
sumif(sheet1 a:a)

When I copy this formula to a new file the formula is:
sumif(c:/mydocs.sales 'sheet1! a:a)

I just want the formula to be:
sumif(sheet1 a:a)

Is this possible?

 

[Dave Peterson]

Copy from the formula bar and paste into the formula bar.

Or change the formula to text
select your range of formulas (more than one cell)
edit replace
what: = (equal sign)
with: $$$$$
replace all

copy and paste

and then change the strings back to formulas (edit|replace--in both worksheets)

 

[vezerid]

Double-click the cell to edit. Copy the formula from the formula bar,
i.e. copy the text of the formula and paste it.

HTH
Kostis Vezerides

 

[JR573PUTT]

Thanks that worked, but not for an array formula where I commit th
formula by hitting ctrl shift enter.

Is there a way to copy an array formula that references a sheet

 

[JR573PUTT]

Copy from the formula bar and paste into the formula bar.

Or change the formula to text
select your range of formulas (more than one cell)
edit replace
what: = (equal sign)
with: $$$$$
replace all

copy and paste

and then change the strings back to formulas (edit|replace--in both
worksheets)

http://www.excelforum.com/showthread.php?threadid=513310

What about an array formular?

 

[Dave Peterson]

You could use code...

Option Explicit
Sub testme()

Dim FromCell As Range
Dim ToCell As Range

Set FromCell = Workbooks("book2.xls").Worksheets("sheet1").Range("a1")
Set ToCell = Workbooks("Book1.xls").Worksheets("sheet1").Range("a1")

If FromCell.HasFormula Then
If FromCell.HasArray Then
ToCell.FormulaArray = FromCell.FormulaArray
Else
ToCell.Formula = FromCell.Formula
End If
End If
End Sub

 

[JR573PUTT]

How do I use this code? Not familiar with code..............

 

[Dave Peterson]

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

 

[JR573PUTT]

Code is Macro, yes I know how to record and run a macro, thanks for the
answer.

 

[JR573PUTT]

If you're new to macros, you may want to read David McRitchie's intr
at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

http://www.excelforum.com/showthread.php?threadid=513310

I figured out a better way:

Highlight source worksheet column,
click copy
go to recipient worksheet and paste
Go to Edit Links, change source worksheet to recipient worksheet,
click ok.

Works great...............thanks again for your hel