You could modify the function like so

Function SHEETOFFSET(offsetStart, offsetEnd, ref) As Variant
Dim iStart As Long
Dim iEnd As Long
Dim i As Long
Dim ary

Application.Volatile
With Application.Caller.Parent
iStart = .Index + offsetStart
iEnd = .Index + offsetEnd
ReDim ary(1 To iEnd - iStart + 1)
For i = iStart To iEnd
ary(i - iStart + 1) = .Parent.Sheets(i).Range(ref.Address).Value
Next i
End With
SHEETOFFSET = ary
End Function


and call like

=SUM(Sheetoffset(0,2,A1))

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Shirish" wrote in message
oups.com...
dear all,
am new to the group (few days old), so apologies for any repitition -
pls guide me to the previous strings in that case.

I am aware of J-Walk's SheetOffset function for obtaining a particular
cell/range reference from a different worksheet. what i'm looking for
is something further. First, an analogy from 'normal' excel use:

1. enter 1, 2, 3 in cell A1 of each of the 3 worksheets (Sheet1, Sheet2
and Sheet3)
2. Enter this formula in any of the other cells: =SUM(Sheet1:Sheet3!A1)
3. Result is obviously 1+2+3 = 6.

I know this works. QUESTION is:
Can i use the above formula so that in place of "Sheet1" and "Sheet3" I
have the SheetOffset() function?
Something like: =SUM(Sheetoffset(0,A1):Sheetoffset(2,A1))?

Thanks,
Shirish