Select both sheets as Jim suggested, then enter this formula:

=Average(Sheet1!A3:A4)

Regards,
Per
"warrenshooter" skrev i
meddelelsen ...
Solve the problem.

Not the greatest code in the world but does the job

Moves the src formula to a temp cell on the source sheet.
Cut and Pastes it to a temp cell on dest cheet (this performs the source
sheet referencing)
Copy and Pastes the temp cell on the dest sheet to the dest cell (this
updates the relative cell references)

Cheers and thanks for everyones help.

Sub CopyFormula()
Dim srcformulaCell As Range
Dim destformulaCell As Range
Dim tempCell_WS1 As Range
Dim tempCell_WS2 As Range

Set srcformulaCell = Worksheets("Sheet1").Range("A5")
Set tempCell_WS1 = Worksheets("Sheet1").Range("Z1")
Set tempCell_WS2 = Worksheets("Sheet2").Range("Z1")
Set destformulaCell = Worksheets("Sheet2").Range("A5")

tempCell_WS1.Formula = srcformulaCell.Formula
tempCell_WS1.Cut
Worksheets("Sheet1").Paste Destination:=tempCell_WS2
Worksheets("Sheet2").Range("Z1").Cut
Worksheets("Sheet2").Paste Destination:=destformulaCell
End Sub





"warrenshooter" wrote:

I have two sheets (Sheet1 and Sheet2)

In cell A5 in Sheet1 I have a formula "=Average(A3,A4)"

I would like to copy this formula to cell A5 in Sheet2 but have the
formula
still reference Sheet1. In cell A5 in Sheet2 the formula should be
"=Average(Sheet1!A3,Sheet1!A4)"

If Sheet1!A5 was originally "=Average(Sheet1!A3,Sheet1!A4)" then there is
no
problem.

Would love to hear the answer. It must be something simple.

Warren