Hi,

I need a reference to a previous sheet where the cell reference is not
absolute - ie automatically updates/changes when I move the information
on the reference sheet. Ie:If I have a reference on Sheet "Week 30
2006" to ='Week 29 2006'!C6+1 and I add a row, it becomes ='Week 29
2006'!C7+1.

The problem is that if I use any of the UDF I have found on the web,
the cell reference doesn't change as I move/add information: eg

Function PrevSheet(rg As Range)
n = Application.Caller.Parent.Index
If n = 1 Then
PrevSheet = 0
Exit Function
End If
PrevSheet = Sheets(Application.Caller.Parent.Index -
1).Range(rg.Address).Value
End Function

'and in the worksheet, enter
'=PrevSheet(E3)
Then PrevSheet(E3) does not become PrevSheet(E4).

I've also tried with indirect:

Function PrevSheet()
On Error GoTo EndFunction
Application.Volatile True
PrevSheet = Application.Caller.Worksheet.Previous.Name
Exit Function
EndFunction:
With Application.Caller.Parent.Parent.Worksheets
PrevSheet = .Item(.Count).Name
End With
End Function

where the worksheet formula becomes =INDIRECT("'"&PrevSheet()&"'!C6")

Again - C6 is absolute.

Can anyone help me with this?

Thank you so much
Helen