Sum of a cell in all worksheets?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Beatnik - ExcelForums.com - 02 Sep 2005 12:06 GMT

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

Reply to this Message

David McRitchie - 02 Sep 2005 12:51 GMT

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

> I usually just use the search here and quickly find what I need, but I
> can't seem to get it work this morning?
[quoted text clipped - 5 lines]
> Thanks,
> Michael

Reply to this Message

Dave Peterson - 02 Sep 2005 14:09 GMT

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.

> Hi Michael,
>
[quoted text clipped - 18 lines]
> > Thanks,
> > Michael

Signature

Dave Peterson

Reply to this Message

Beatnik - ExcelForums.com - 02 Sep 2005 14:08 GMT

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

Reply to this Message

Beatnik - ExcelForums.com - 02 Sep 2005 16:07 GMT

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

Excellent, Thanks again Dave and David!
Michael

Reply to this Message

Dave Peterson - 02 Sep 2005 18:32 GMT

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

> Didn't see your post Dave?? That will work! I could probably just hide
> those two sheets.
>
> Excellent, Thanks again Dave and David!
> Michael

Signature

Dave Peterson

Reply to this Message