Dynamically/recursively adding across sheets

[southbeach]

Hope I am phrasing this correctly but bare with me and I will try to explain

I have one excel table with multiple sheets (1 through 31), one sheet for each day of the month. Each sheet is used to catalog students earned points based on a number of criteria.

It has become very cumbersome to edit each sheet when a change is made to any of the previous sheets - You see, sheet 2 has a column meant to be a carried over or accumulated total. So, by sheet 20, this column should be the total for a given cell from sheet 1 through sheet 19 ... Am I making sense?

I would love to have a formula that handles the tabulation dynamically and recursively without my having to manually edit each sheet.

Given sheets being named 1,2,3,4,5,6,7... I though of using
something like

@sum(1!a20+2!a20+3!a20+4!a20+5!a20...)

Not sure if this is correct syntax so I figure I reach out for advise before I take the leap into changing everything we have.

Thank you all in advance for your assistance!



--
SouthBeach
The good thing about not knowing is the opportunity to learn - Yours truly, 2008.

 

[SkipVought]

Hi,

You've shot yourself in the foot with your workbook design! You are experiencing the pain and suffering that comes with a design that defeats the plethora of rich data analysis and data reporting features in Excel. You will continue to experience such wasted effort and added maintenance, unless you make some fundamental and necessary changes.

Chopping you data up into separate sheets is what is killing you. Consolidate ALL your data in one table (sheet). Then we can talk about how to summarize your data. I am referring to your data for the YEAR, not just the month. In fact, there is usually no reason to start a new table for a new year, because it renders the ability to do yearly analysis & comparing to previous years' statistics extremely difficult.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[southbeach]

Ouch!

I am not sure I can combine the sheets into a single table - My wife volunteers with the local school and often asks me to help with things such as this. I do not have a problem helping them out when/where I can.

Truth is that I rarely ever use excel and know little to nothing about it. That being said, I would not know the best way to design an excel table much less a bunch of volunteer house wives.

Well, I thought I give it a try. Thanks!


--
SouthBeach
The good thing about not knowing is the opportunity to learn - Yours truly, 2008.

 

[SkipVought]

You must add a column for a DATE ( a REAL DATE not just 1, 2, 3 for the days of week) faq68-5827. Believe me, it will be well worth the bother of doing this consolidation. The maintence and HUGE roadblocks you face manipulating the data as it is will eat your lunch!

So here's the drill.

1. Make a new sheet.

2. Make A1 the heading for your dates.

3. Copy the headings for one of your daily sheet and pasts in B1. Now you have all your headings

4. Do this for each of the 31 sheets...

a. Copy the DATA (excluding the headings) and paste into the next empty row in column B

b. Select the corresponding rows in column A (top to bottom) and enter the DATE ending with shift+ENTER rather than simple ENTER. The shift+ENTER propogates the enterd value in ALL of the selected cells.

This may seem like alot of work, but it will save you grief and pain in the long run.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}