Custom Function not updating

[lister_d_000169]

I have a rather sizeable workbook that contains a lot of sales info. Split by week, by measure in rows. I have a summary sheet that has a dropdown for a week to be selected. this pulls through to another sheet to calcuate a YTD sum from the sales info.

I wrote a custom function that takes the week entered and then uses that value to offset back up teh sheet and add the reqd values and put the result in the selected cell.

Function YTD(WeekNum As Integer)

Dim x, offsetnum As Integer
Dim YTDValue As Double

x = 1
offsetnum = -1328
YTDValue = 0

For x = 1 To WeekNum


YTDValue = YTDValue + ActiveCell.Offset(offsetnum, 0).Value
offsetnum = offsetnum + 16

Next

YTD = YTDValue

End Function

the function works fine if i manually enter it into the cells. If however i change th week number the formula does not update, i get #value!. my suspicion is because the function is setup using Activecell.offset and those cells are not active.

anyone have any ideas??

[Charles Williams]

suggest you:

Add Application.Volatile to your function if you cannot find a way of of
entering the offset cell as a function argument.

replace activecell with application.caller so that the function is working
from the cell that contains it rather than whatevere the active cell happens
to be.

Charles
______________________
Decision Models
FastExcel 2.2 Beta now available
www.DecisionModels.com

"lister_d_000169"
<lister_d_000169.25g50v_1143661227.8584@excelforum-nospam.com> wrote in
message news:lister_d_000169.25g50v_1143661227.8584@excelforum-nospam.com...
>
> I have a rather sizeable workbook that contains a lot of sales info.
> Split by week, by measure in rows. I have a summary sheet that has a
> dropdown for a week to be selected. this pulls through to another
> sheet to calcuate a YTD sum from the sales info.
>
> I wrote a custom function that takes the week entered and then uses
> that value to offset back up teh sheet and add the reqd values and put
> the result in the selected cell.
>
> Function YTD(WeekNum As Integer)
>
> Dim x, offsetnum As Integer
> Dim YTDValue As Double
>
> x = 1
> offsetnum = -1328
> YTDValue = 0
>
> For x = 1 To WeekNum
>
>
> YTDValue = YTDValue + ActiveCell.Offset(offsetnum, 0).Value
> offsetnum = offsetnum + 16
>
> Next
>
> YTD = YTDValue
>
> End Function
>
> the function works fine if i manually enter it into the cells. If
> however i change th week number the formula does not update, i get
> #value!. my suspicion is because the function is setup using
> Activecell.offset and those cells are not active.
>
> anyone have any ideas??
>
>
> --
> lister_d_000169
> ------------------------------------------------------------------------
> lister_d_000169's Profile:
> http://www.excelforum.com/member.php...o&userid=32950
> View this thread: http://www.excelforum.com/showthread...hreadid=527771
>

[lister_d_000169]

Charles

you are a legend !!!! application.caller did the job

many thanks

rgds

mark