I have a workbook that I am trying to use a user defined function to always
reference the previous sheet so I can make several copies of the sheet to
cover a 52 week period. I have made it this far. However when I change the
data and then go to the next sheet the calculations do not automatically
update. I have tried to refresh the data and re-calculate the sheet but the
calculation does not update. Any help would be appreciated.

JMC

I don't seem to have a problem with this UDF not updating.

Is yours similar?

Function PrevSheet(rg As Range)
Application.Volatile
n = Application.Caller.Parent.Index
If n = 1 Then
PrevSheet = CVErr(xlErrRef)
ElseIf TypeName(Sheets(n - 1)) = "Chart" Then
PrevSheet = CVErr(xlErrNA)
Else
PrevSheet = Sheets(n - 1).Range(rg.Address).Value
End If
End Function


Gord Dibben MS Excel MVP

On Tue, 5 Sep 2006 14:49:01 -0700, JMC wrote:

I have a workbook that I am trying to use a user defined function to always
reference the previous sheet so I can make several copies of the sheet to
cover a 52 week period. I have made it this far. However when I change the
data and then go to the next sheet the calculations do not automatically
update. I have tried to refresh the data and re-calculate the sheet but the
calculation does not update. Any help would be appreciated.

Sorry its taken so long to respond. Work, Work, Work
"Application.Volatile"
missing from the UDF i had. once i put that in everything worked just fine.
thanks for taking the time to help me out. People like you make life easier
for amatures like me.

thanks again
JMC



"Gord Dibben" wrote:

JMC

I don't seem to have a problem with this UDF not updating.

Is yours similar?

Function PrevSheet(rg As Range)
Application.Volatile
n = Application.Caller.Parent.Index
If n = 1 Then
PrevSheet = CVErr(xlErrRef)
ElseIf TypeName(Sheets(n - 1)) = "Chart" Then
PrevSheet = CVErr(xlErrNA)
Else
PrevSheet = Sheets(n - 1).Range(rg.Address).Value
End If
End Function


Gord Dibben MS Excel MVP

On Tue, 5 Sep 2006 14:49:01 -0700, JMC wrote:

I have a workbook that I am trying to use a user defined function to always
reference the previous sheet so I can make several copies of the sheet to
cover a 52 week period. I have made it this far. However when I change the
data and then go to the next sheet the calculations do not automatically
update. I have tried to refresh the data and re-calculate the sheet but the
calculation does not update. Any help would be appreciated.