I have a workbook with n worksheets. I would like to sum the cells from
Sheet2 to Sheet(n-1). The names of sheets 1 and n are static, but names on
other sheets change. Is there any way to do a 3D reference with an offset in
the worksheet specifier, so that the formula works regardless of the number
or names of the worksheets in the middle? Something like
=SUM(('Sheet1'+1):('SheetN'-1)!O4)

One way:

Put a dummy/blank worksheet after Sheet1 and another one before SheetN,
perhaps named "Start" and "End". Use the formula

=SUM('Start:End'!O4)

put as many sheets in the middle as you like. You can hide the dummy
sheets if you like.


In article ,
pfrieder wrote:

I have a workbook with n worksheets. I would like to sum the cells from
Sheet2 to Sheet(n-1). The names of sheets 1 and n are static, but names on
other sheets change. Is there any way to do a 3D reference with an offset in
the worksheet specifier, so that the formula works regardless of the number
or names of the worksheets in the middle? Something like
=SUM(('Sheet1'+1):('SheetN'-1)!O4)

Hi,

You could use something like this:

=SUM(Sheet1:SheetN!O4)-Sheet1!O4-SheetN!O4

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"pfrieder" wrote:

I have a workbook with n worksheets. I would like to sum the cells from
Sheet2 to Sheet(n-1). The names of sheets 1 and n are static, but names on
other sheets change. Is there any way to do a 3D reference with an offset in
the worksheet specifier, so that the formula works regardless of the number
or names of the worksheets in the middle? Something like
=SUM(('Sheet1'+1):('SheetN'-1)!O4)

Thanks, this is a good suggestion. In my case, however, SheetN is where the
summation is located so this approach would produce a circular reference. I
still wonder if there is any mechanism that actually offsets a worksheet
reference, or if I am stuck with using a work-around.

"Shane Devenshire" wrote:

Hi,

You could use something like this:

=SUM(Sheet1:SheetN!O4)-Sheet1!O4-SheetN!O4

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"pfrieder" wrote:

I have a workbook with n worksheets. I would like to sum the cells from
Sheet2 to Sheet(n-1). The names of sheets 1 and n are static, but names on
other sheets change. Is there any way to do a 3D reference with an offset in
the worksheet specifier, so that the formula works regardless of the number
or names of the worksheets in the middle? Something like
=SUM(('Sheet1'+1):('SheetN'-1)!O4)

Thanks, this will work for my current effort. I wonder, though, if Excel has
any mechanism to offset a worksheet reference in a function call. One can
achieve something similar in VB, e.g.

lastSht = Sheets("All Tasks").Index - 1

You can use offsets to reference a cell or range, doesn't seem like much of
a stretch to extend the concept to worksheets.

"JE McGimpsey" wrote:

One way:

Put a dummy/blank worksheet after Sheet1 and another one before SheetN,
perhaps named "Start" and "End". Use the formula

=SUM('Start:End'!O4)

put as many sheets in the middle as you like. You can hide the dummy
sheets if you like.


In article ,
pfrieder wrote:

I have a workbook with n worksheets. I would like to sum the cells from
Sheet2 to Sheet(n-1). The names of sheets 1 and n are static, but names on
other sheets change. Is there any way to do a 3D reference with an offset in
the worksheet specifier, so that the formula works regardless of the number
or names of the worksheets in the middle? Something like
=SUM(('Sheet1'+1):('SheetN'-1)!O4)

No, XL doesn't have that functionality (I would probably have used it if
it did...).

XL's 3-D capability is very limited. And VBA's is non-existent.

In article ,
pfrieder wrote:

Thanks, this will work for my current effort. I wonder, though, if Excel has
any mechanism to offset a worksheet reference in a function call. One can
achieve something similar in VB, e.g.

lastSht = Sheets("All Tasks").Index - 1

You can use offsets to reference a cell or range, doesn't seem like much of
a stretch to extend the concept to worksheets.