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

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

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

Bob Phillips wrote:
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

"Shirish" wrote in message
ups.com...


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