Applying J-Walk's SheetOffset() to SUM across sheets - possible?

S

Shirish

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
 
B

Bob Phillips

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

Shirish

Bob,

BINGO! it worked precisely as i had hoped it should. more importantly,
it has given me the courage to undertake - or at least attempt to
undertake - further modifications to the function if needed.

Sincere thanks!

on a personal note, i saw your profile and the (number of)
contributions to the group. must say, am impressed!

cheers
shirish
 
B

Bob Phillips

BINGO! it worked precisely as i had hoped it should. more importantly,
it has given me the courage to undertake - or at least attempt to
undertake - further modifications to the function if needed.
Click to expand...


Excellent! Glad you will take it further; you know where to look if you get
further problems <G>

Bob
 
Top