how to update data displayed from a UDF when the source data chang

[JMC]

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.

 

[Gord Dibben]

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

 

[JMC]

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