Collating from worksheets

[Mal]

I have a large file that contains 28 different worksheets regarding
individuals.
On each worksheet is collated a number of sums from the data to give me
info.

Is it possible to have a 'summary' worksheet, listing the total of all the
worksheets from the same cell on each worksheet on the summary sheet?
I'm trying to save having to go through each worksheet and manually add the
figures.

Mal

 

[L. Howard Kittle]

Hi Mal,

=SUM(Sheet1:Sheet28!F1)

Will add all the F1's in sheet 1 to 28.

HTH
Regards,
Howard

 

[Mal]

ermmm...it came up with name error!!

Is it because my sheets are named not just sheet1 etc?
Or is it because the cell i want to copy is a calculation?

Mal

 

[Mal]

Dont panic... i fiddled and fixed it!! Its the names!

Mal

 

[L. Howard Kittle]

Hi Mal,

Yes, it must be the actual name of the sheets. In this example the sheet
named Mice is the first sheet and Cats is the 28th sheet.

=SUM(Mice:Cats!F1)

A quick way to make this formula is to type =SUM( into the cell you want the
totals in and then select the first sheet and with the shift key held down
select the last sheet. All the sheet tabs should be highlighted. Release
the shift key and click on the cell you want to sum. Now add the ) to the
formula and hit enter. This assumes you are totaling 28 sheets and the
formula is on a sheet other than the 28. 29 sheets in all.

If you are totaling on the first of 28 sheets type in =SUM( and hold down
the shift key and select the last sheet and then the cell and then the ) and
then enter.

HTH
Regards,
Howard

 

[Dave Peterson]

I like this technique...

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

Call them Start and End

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

=sum(start:end!B37)

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.

 

[Mal]

How can i drag a sheet??

Thanks to both for your help.

Mal

 

[Roger Govier]

Hi Mal

Click on sheet tab.
Hold left mouse button down.
Drag to required location.
Release.

Regards

Roger Govier

How can i drag a sheet??

Thanks to both for your help.

Mal

 

[Mal]

Sometimes its soooooo. simple!!!

Thanks.....

Mal

Hi Mal

Click on sheet tab.
Hold left mouse button down.
Drag to required location.
Release.

Regards

Roger Govier

 

[Ron de Bruin]

Tip: When you press the Ctrl key when you do this you create a copy of the sheet