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

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

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

www.OfficeArticles.com
www.MyExpertsOnline.com


"gwhite" wrote in
message ...

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!


--
gwhite
------------------------------------------------------------------------
gwhite's Profile: http://www.excelforum.com/member.php...o&userid=25073
View this thread: http://www.excelforum.com/showthread...hreadid=385898