Applying J-Walk's SheetOffset() to SUM across sheets - possible?
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 |
Applying J-Walk's SheetOffset() to SUM across sheets - possible?
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 |
Applying J-Walk's SheetOffset() to SUM across sheets - possible?
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 |
Applying J-Walk's SheetOffset() to SUM across sheets - possible?
"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 |
All times are GMT +1. The time now is 03:48 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com