Sum of a cell in all worksheets?

  • Thread starter Beatnik - ExcelForums.com
  • Start date
B

Beatnik - ExcelForums.com

I usually just use the search here and quickly find what I need, but
can't seem to get it work this morning

I am trying to get a sum of a certain cell in all the sheets in
workbook. For example of what I mean, Sum(all worksheets, d64). Ca
someone possibly help me out

Thanks
Michae
 
D

David McRitchie

Hi Michael,

Look up 3D in your Excel Help.
Surely you do not want to include the same sheet.

if sheet2 is your second sheet tab and "sheet 24" is the last tab.
=SUM(sheet2:'sheet 24'!C14)
 
B

Beatnik - ExcelForums.com

It really doesn't matter if it includes the same sheet, the cell
refered to is blank on that sheet. The problem is that daily I add
new sheets and quarterly I delete old sheets. I am currently doing it
the way you suggest =SUM(sheet2:'sheet 24'!C14). At each quarter end
I have to adjust the formulas to include the new sheets and not the
old deleted ones. I would like to have the luxury of not having to
mess with this everytime if possible.

Michael
 
D

Dave Peterson

I think David meant:

=SUM('sheet2:sheet 24'!C14)

==
Another option based on David's suggestion.

Create two new worksheets--one to the far right and one to the far left.

Call them Start and End

Then using a sheet that is outside this "sandwich" of worksheets:

=sum(start:end!c14)

Then you can drag sheets in and out of that sandwich to play what if games.

I'd put a couple of notes on each of these sheets:

"don't delete this sheet!"

And protect the worksheet so that people don't use it for real data.

David said:
Hi Michael,

Look up 3D in your Excel Help.
Surely you do not want to include the same sheet.

if sheet2 is your second sheet tab and "sheet 24" is the last tab.
=SUM(sheet2:'sheet 24'!C14)
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

Beatnik - ExcelForums.com said:
I usually just use the search here and quickly find what I need, but I
can't seem to get it work this morning?

I am trying to get a sum of a certain cell in all the sheets in a
workbook. For example of what I mean, Sum(all worksheets, d64). Can
someone possibly help me out?

Thanks,
Michael
Click to expand...
Click to expand...
 
B

Beatnik - ExcelForums.com

Didn't see your post Dave?? That will work! I could probably just hide
those two sheets.

Excellent, Thanks again Dave and David!
Michael
 
D

Dave Peterson

Personally, I'd keep those sheets visible. It really makes it easier to see
what's being summed.
 
Top