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

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

JMC - 05 Sep 2006 22:49 GMT

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.

Reply to this Message

Gord Dibben - 06 Sep 2006 00:50 GMT

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

>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.

Reply to this Message

JMC - 09 Sep 2006 01:38 GMT

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

> JMC
>
[quoted text clipped - 22 lines]
> >update. I have tried to refresh the data and re-calculate the sheet but the
> >calculation does not update. Any help would be appreciated.

Reply to this Message

Rate this thread: