Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 --- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How To make a sheet reference Variable (eq: sum(sheet!D2:H2)) | Excel Discussion (Misc queries) | |||
VLOOKUPS: Limits on # per sheet? | Excel Discussion (Misc queries) | |||
variable in a link where the variable is the name of the sheet | Excel Worksheet Functions | |||
How to use variable in reference | Excel Worksheet Functions | |||
Cell reference - for the sheet name, can I use a variable? | Excel Discussion (Misc queries) |