SUM 3D Reference using variable sheet limits
 

I want to SUM the values in the X35 cells from multiple sheets named for
months (Nov2008 . . May2009 etc.). I need to be able to easily change the
limits of the sheets to be included in the calculation, (ie: Nov2008 . . May
2009, then recalculate for Jan2009 . . April2009).

AD31 will contain the name of the first included sheet ('Nov2008), AF31 will
contain the name of the last included sheet ('May2009).

=SUM(INDIRECT(AD31:AF31&"!X35"))
returns an incorrect number. I think it is missing the fact that the cell
references refer to sheets, not cells. What formula or modification do I
need?

SUM 3D Reference using variable sheet limits
 

I'd do it in this manner, for better clarity and easier manipulation of
desired summarizations

In a summary sheet,
List all the source sheetnames in B1 across, eg: Nov2008, ...
List all the cell references in A2 down, eg X35, ...

Then extract it clearly and easily
in one swoop from each source sheet by putting in B2:
=INDIRECT("'"&B$1&"'!"&$A2)
and copy B2 across/fill down as far as required

Then just proceed to do the row-wise SUM (or whatever)
in an adjacent col to the right
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:21,000 Files:370 Subscribers:66
xdemechanik
---
"Loge" wrote:
I want to SUM the values in the X35 cells from multiple sheets named for
months (Nov2008 . . May2009 etc.). I need to be able to easily change the
limits of the sheets to be included in the calculation, (ie: Nov2008 . . May
2009, then recalculate for Jan2009 . . April2009).

AD31 will contain the name of the first included sheet ('Nov2008), AF31 will
contain the name of the last included sheet ('May2009).

=SUM(INDIRECT(AD31:AF31&"!X35"))
returns an incorrect number. I think it is missing the fact that the cell
references refer to sheets, not cells. What formula or modification do I
need?

SUM 3D Reference using variable sheet limits
 

Try this:
*
=SUM(INDIRECT(AD31&":"&AF31&"!X35"))*


--
JBeaucaire
------------------------------------------------------------------------
JBeaucaire's Profile: http://www.thecodecage.com/forumz/member.php?userid=73
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=45397

SUM 3D Reference using variable sheet limits
 

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"Loge" wrote:

I want to SUM the values in the X35 cells from multiple sheets named for
months (Nov2008 . . May2009 etc.). I need to be able to easily change the
limits of the sheets to be included in the calculation, (ie: Nov2008 . . May
2009, then recalculate for Jan2009 . . April2009).

AD31 will contain the name of the first included sheet ('Nov2008), AF31 will
contain the name of the last included sheet ('May2009).

=SUM(INDIRECT(AD31:AF31&"!X35"))
returns an incorrect number. I think it is missing the fact that the cell
references refer to sheets, not cells. What formula or modification do I
need?

SUM 3D Reference using variable sheet limits
 

I just get the #REF error.

The sheet names in the two cells are entered with the ' infront, just like I
usually do with INDIRECT when I'm not using a 3D reference. I don't know of
any other formatting to trick INDIRECT into accepting the range reference.

"JBeaucaire" wrote:


Try this:
*
=SUM(INDIRECT(AD31&":"&AF31&"!X35"))*


--
JBeaucaire
------------------------------------------------------------------------
JBeaucaire's Profile: http://www.thecodecage.com/forumz/member.php?userid=73
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=45397

SUM 3D Reference using variable sheet limits
 

This would work, but it means that I wolud have to update the summary sheet
and re-populate the new cells for every new inquiry. I was hoping for a way
to simply enter the end-sheet limits and have the cell formula automatically
update its output.

"Max" wrote:

I'd do it in this manner, for better clarity and easier manipulation of
desired summarizations

In a summary sheet,
List all the source sheetnames in B1 across, eg: Nov2008, ...
List all the cell references in A2 down, eg X35, ...

Then extract it clearly and easily
in one swoop from each source sheet by putting in B2:
=INDIRECT("'"&B$1&"'!"&$A2)
and copy B2 across/fill down as far as required

Then just proceed to do the row-wise SUM (or whatever)
in an adjacent col to the right
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:21,000 Files:370 Subscribers:66
xdemechanik
---
"Loge" wrote:
I want to SUM the values in the X35 cells from multiple sheets named for
months (Nov2008 . . May2009 etc.). I need to be able to easily change the
limits of the sheets to be included in the calculation, (ie: Nov2008 . . May
2009, then recalculate for Jan2009 . . April2009).

AD31 will contain the name of the first included sheet ('Nov2008), AF31 will
contain the name of the last included sheet ('May2009).

=SUM(INDIRECT(AD31:AF31&"!X35"))
returns an incorrect number. I think it is missing the fact that the cell
references refer to sheets, not cells. What formula or modification do I
need?

SUM 3D Reference using variable sheet limits
 

"Loge" wrote:
This would work, but it means that I would have to update the summary sheet
and re-populate the new cells for every new inquiry. I was hoping for a way
to simply enter the end-sheet limits and have the cell formula automatically
update its output.

But you can easily see what's happening (ie what's being extracted from each
source sheet) and just adjust your row-wise SUM formula as required to cover
whatever cols that's supposed to be included in the sum. And that adjustment
shouldn't take more effort than the way you mention. Don't you want the
clarity that's afforded?
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:21,000 Files:370 Subscribers:66
xdemechanik
---