Appending a total from multiple sheets in Excel.

[fiel]

I have multiple sheets where a single sum of multiple values for each sheet is displayed at the top. I'm trying to create a 'Summary Sheet' that will add all those values together and dislpay it in one cell. The only thing is that at times I'll be adding and removing extra sheets. Is there a way to have it so that my cell keeping the summary can update to these changing values as sheets are added and removed?

 

[Hasit]

Not sure I understand. If you have a sum function in sheet1 and its summing cells in sheet2, sheet3 and sheet4, then adding or removing sheets will not cause any problems. As long as, you do not rename your sheets.

i.e. if your sheet is called "sheet3" for example, then as long as you do not rename it, adding more sheets will maintain the integrity of the sum function.

Can you provide more detail in case I have misunderstood.

 

[SkipVought]

fiel,

FYI...

This is like a broken record, because it reflects a typical mistake that spreadsheet users often make.

"I'm trying to create a 'Summary Sheet' that will add all those values together and dislpay it in one cell. The only thing is that at times I'll be adding and removing extra sheets."

My friend, you have the cart before the horse. Your workbook design ought to have at lease one table of source data, from which your various sheets (REPORTS), could be generated. This is the way that Excel works best. ONE source. MANY reports.

If you were to have such a design, the answer to your question would be relatively simple and could probably be generated in a matter of SECONDS with on of Excel's many analysis and reporting tools, like PivotTable, Subtotals, Query, Database functions, Filters.

Skip,

_[red][/red]

 

[GlennUK]

Have a "Starting" sheet and an "Ending" sheet, and sum between those sheets, and put new sheets in between those two, and 3-d summing will work as you require.

By the way, I have to agree with Skip, in that this sounds as if there is a design problem if you are keeping lumps of data in separate sheets.

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[fiel]

Thanks Glenn, I'm trying to go in the direction you mentioned. But should I try to write a macro for this or is there a way to put the formula directly in a cell? The thing is that the sheets would be named as 'List 1', 'List 2', 'List 3', and so forth with the last sheet named as 'List X'.

 

[Rofeu]

Fiel,

GlennUK means that you add 2 blank sheets. One at the beginning of the file called "Start" and another at the end of the file called "End". Adding new sheets will be just before "End".

You can then use a 3D formula, i.e.: =SUM(Start:End!A1)

Cheers,

Roel