Is it possible to offset a worksheet reference?


P

pfrieder

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

Advertisements

J

JE McGimpsey

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

Shane Devenshire

Hi,

You could use something like this:

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

pfrieder

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

pfrieder

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

Advertisements

J

JE McGimpsey

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

Advertisements


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top