I am looking for a way to sum some values from a number of different worksheets on a "summary" worksheet. I know this sounds simple, and I know of a "long way" to do it, but I'm wondering if anyone has a more elegant solution out there. Here's the problem:
FILE FRAMEWORK: I have a workbook that contains 33 worksheets. The worksheets are titled:
- "S" this is my summary worksheet
- "B" this is a begin worksheet (for 3d sum)
- "1" - "30": these are data sheets that contain calculations. Each sheet has a column of dates in column "B" and data that corresponds to those dates in columns "D" - "Z", i.e. the value in cell "D4" corresponds to the date in cell "B4". The dates in column "B" are always the first of the month, and each row down is exactly 1 month after the cell above it, i.e. if cell B4 contains the value 1/1/10, cell B5 contains the value 2/1/10. This file structure works because the dates in column "B" on EVERY sheet are the same, i.e. if cell "B10" on sheet "S" is Jan/11, cell "B10" on ALL the worksheets is Jan/11.
- "E" this is an end worksheet (for 3d sum)
SSUMMARY SHEET: The sheet "S" contains a 3d sum formula that sums a given cell on all the worksheets between "B" and "E", i.e. on sheet "S" cell "E10" contains the formula: =SUM(B:E!E10). So, every sheet that I position in between "B" and "E" is included in the sum. If I want to exclude a sheet from the sum, I simply move it to the right of the sheet "E"
PROBLEM: I have a file where the "B" column on each sheet is NOT the same, i.e if cell "B10" on sheet "S" is Jan/11, cell "B10" on ALL the worksheets may not be Jan/11. I would like sheet "S" to do a 3d sum for all the sheets contained between sheets "B" and "E" for the given date, i.e. if cell "B10" on sheet "S" contains the date Jan/11, cell "D10" on sheet "S" will contain a formula that sums the value in column "D" that corresponds to Jan/11 on each sheet between "B" and "E".
Is this possible?
The "long way" I can think of to do this is to create a sheet that "moves" the data up or down, depending on the date in cell "B1" on the given sheet, then sums across. This not only takes up a ton of space, it is not dynamic like my other sheets are, and there is potential for error if someone else is using the sheet.
Please help
FILE FRAMEWORK: I have a workbook that contains 33 worksheets. The worksheets are titled:
- "S" this is my summary worksheet
- "B" this is a begin worksheet (for 3d sum)
- "1" - "30": these are data sheets that contain calculations. Each sheet has a column of dates in column "B" and data that corresponds to those dates in columns "D" - "Z", i.e. the value in cell "D4" corresponds to the date in cell "B4". The dates in column "B" are always the first of the month, and each row down is exactly 1 month after the cell above it, i.e. if cell B4 contains the value 1/1/10, cell B5 contains the value 2/1/10. This file structure works because the dates in column "B" on EVERY sheet are the same, i.e. if cell "B10" on sheet "S" is Jan/11, cell "B10" on ALL the worksheets is Jan/11.
- "E" this is an end worksheet (for 3d sum)
SSUMMARY SHEET: The sheet "S" contains a 3d sum formula that sums a given cell on all the worksheets between "B" and "E", i.e. on sheet "S" cell "E10" contains the formula: =SUM(B:E!E10). So, every sheet that I position in between "B" and "E" is included in the sum. If I want to exclude a sheet from the sum, I simply move it to the right of the sheet "E"
PROBLEM: I have a file where the "B" column on each sheet is NOT the same, i.e if cell "B10" on sheet "S" is Jan/11, cell "B10" on ALL the worksheets may not be Jan/11. I would like sheet "S" to do a 3d sum for all the sheets contained between sheets "B" and "E" for the given date, i.e. if cell "B10" on sheet "S" contains the date Jan/11, cell "D10" on sheet "S" will contain a formula that sums the value in column "D" that corresponds to Jan/11 on each sheet between "B" and "E".
Is this possible?
The "long way" I can think of to do this is to create a sheet that "moves" the data up or down, depending on the date in cell "B1" on the given sheet, then sums across. This not only takes up a ton of space, it is not dynamic like my other sheets are, and there is potential for error if someone else is using the sheet.
Please help
