Attn: Excel gurus

Moonliner · 11-21-2005, 09:52 PM

I must defend my title!!

SUM ALL SHEETS

The first one is how we can use the SUM function to Sum, say cell A1, on all Worksheets in your Workbook. With this method, all new sheets that are added to the Workbook are included in the SUM. Here is how;

Add a new Worksheet to you Workbook at the far right. To do this select "Worksheet" under "Insert" on the Worksheet Menu Bar. Then left click on this new Worksheet name tab, holding down the the left mouse button drag to the far right and release.

Name this sheet (double click the name tab) "Spacer" (although any name will do).

With "Spacer" being the active sheet, go to Format>Sheet>Hide.

Repeat steps 1 to 3 above, but this time name the Worksheet "Start" and drag it to the far left.

On the Worksheet you wish to have the result shown on enter: =SUM('Start:Spacer'!A1)

Add any number of new Worksheets, enter some numbers into A1 of any Worksheet and it will be included in the SUM. Move the Worksheets order around, Delete sheets etc and the SUM function still includes all Worksheets.

If you have concerns that a user may unhide/delete/move the Worksheets "Start & "Spacer", go to Tools>Protection>Protect Workbook (ensure "Structure" is checked), supply a password (optional) and click OK.

OK, OK, go I goolged this one. It still counts right?

Kevy Baby · 11-22-2005, 07:49 AM

Quote:

Originally Posted by Moonliner

I must defend my title!!

You have to defend "Trend Bucker"?

What the buck?!?

Moonliner · 11-22-2005, 08:31 AM

Quote:

Originally Posted by Kevy Baby

You have to defend "Trend Bucker"?

What the buck?!?

No you silly rabbit, not that title. This title.

11-21-2005, 09:52 PM	#1
Moonliner 8/30/14 - Disneyland -10k or Bust. Join Date: Jan 2005 Posts: 9,022	I must defend my title!! SUM ALL SHEETS The first one is how we can use the SUM function to Sum, say cell A1, on all Worksheets in your Workbook. With this method, all new sheets that are added to the Workbook are included in the SUM. Here is how; Add a new Worksheet to you Workbook at the far right. To do this select "Worksheet" under "Insert" on the Worksheet Menu Bar. Then left click on this new Worksheet name tab, holding down the the left mouse button drag to the far right and release. Name this sheet (double click the name tab) "Spacer" (although any name will do). With "Spacer" being the active sheet, go to Format>Sheet>Hide. Repeat steps 1 to 3 above, but this time name the Worksheet "Start" and drag it to the far left. On the Worksheet you wish to have the result shown on enter: =SUM('Start:Spacer'!A1) Add any number of new Worksheets, enter some numbers into A1 of any Worksheet and it will be included in the SUM. Move the Worksheets order around, Delete sheets etc and the SUM function still includes all Worksheets. If you have concerns that a user may unhide/delete/move the Worksheets "Start & "Spacer", go to Tools>Protection>Protect Workbook (ensure "Structure" is checked), supply a password (optional) and click OK. OK, OK, go I goolged this one. It still counts right? __________________ - Taking it one step at a time.
	Submit to Quotes