Averaging using relative references across sheets

A

amckale

I am trying to average across spreadsheets using a relative sheet
reference.

I use this function (which I found on this forum a few days ago) for
lots of references and was trying to use it in the averaging formula
with no luck.

Function SHEETOFFSET(offset, Ref)
' Returns cell contents at Ref, in sheet offset
Application.Volatile
With Application.Caller.Parent
SHEETOFFSET = .Parent.Sheets(.Index + offset) _
.Range(Ref.Address).Value
End With
End Function


The averaging formula in its absolute format looks like this:

=+AVERAGE(April:January!D138)

the modified formula trying to make this relative is:

=AVERAGE(April:SHEETOFFSET(0,D138))

but it dosent work..

any help would be appreciated.

Thanks
Raj
 
Top