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

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@excelfor um-nospam.com wrote in
message news:lister_d_000169.25g50v_1143661227.8584@excelf orum-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

Charles

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

many thanks

rgds

mark


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