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