Summary Sheet Totals

I have a workbook that tallys values from 30 or so separate sheets. Is there an easy way to have a summary sheet that looks at specific cells on each sheet? Right now, I have to do a simple SUM function where I manually click on each cell on each sheet I want in the total. I'm thinking there is a way to do this with a formula/array.

Thanks,

Gary

Here you go:
http://www.officearticles.com/excel/...worksheets.htm

*******************
~Anne Troy

www.OfficeArticles.com
www.MyExpertsOnline.com


"gwhite" <[email protected]> wrote in
message news:[email protected]...
>
> I have a workbook that tallys values from 30 or so separate sheets. Is
> there an easy way to have a summary sheet that looks at specific cells
> on each sheet? Right now, I have to do a simple SUM function where I
> manually click on each cell on each sheet I want in the total. I'm
> thinking there is a way to do this with a formula/array.
>
> Thanks,
>
> Gary
>
>
> --
> gwhite
> ------------------------------------------------------------------------
> gwhite's Profile:
http://www.excelforum.com/member.php...o&userid=25073
> View this thread: http://www.excelforum.com/showthread...hreadid=385898
>
>

Are they always in the same position (summing A1 of all the sheets or b22 of all
sheets)?

If yes...

You create two "helper" worksheets. One named Start and one named Finish.
(Lock all the cells and protect these two worksheets--just so no one uses them.)

Put all your "data" worksheets (and no others!) between those two worksheets.

Then you can use this on the summary sheet (outside that "sandwich")

=sum(Start:Finish!a1)

The users could even play what-if games. If they drag a data worksheet out of
the Start:Finish sandwich, then that worksheet's values will be ignored.

gwhite wrote:
>
> I have a workbook that tallys values from 30 or so separate sheets. Is
> there an easy way to have a summary sheet that looks at specific cells
> on each sheet? Right now, I have to do a simple SUM function where I
> manually click on each cell on each sheet I want in the total. I'm
> thinking there is a way to do this with a formula/array.
>
> Thanks,
>
> Gary
>
> --
> gwhite
> ------------------------------------------------------------------------
> gwhite's Profile: http://www.excelforum.com/member.php...o&userid=25073
> View this thread: http://www.excelforum.com/showthread...hreadid=385898

--

Dave Peterson

Great Stuff! Works perfectly - thanks for the tip!