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

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

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Shirish - 07 Nov 2006 09:19 GMT

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

Reply to this Message

Bob Phillips - 07 Nov 2006 10:13 GMT

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

Signature

HTH

Bob Phillips

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

> dear all,
> am new to the group (few days old), so apologies for any repitition -
[quoted text clipped - 16 lines]
> Thanks,
> Shirish

Reply to this Message

Shirish - 08 Nov 2006 10:08 GMT

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

> You could modify the function like so
>
[quoted text clipped - 47 lines]
> > Thanks,
> > Shirish

Reply to this Message

Bob Phillips - 08 Nov 2006 10:27 GMT

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

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

Bob

Reply to this Message