excel advanced training - Sum through worksheets

The UK's most regular instructor-led training courses.
Training information: excel advanced training · Excel training course · Microsoft Excel Training
See also · excel-courses-london · excel courses in london · excel microsoft training

Resolved · Low Priority · Version Standard

Sum through worksheets

Dawn has attended:
Excel Advanced course

by - delegate Dawn [1 post] (2007 Nov 7 Wed, 10:18) Reply

How do I sum the same cell through various worksheets.

Version	Date	Location	Places available	Book	Next place rate:
Excel VBA 2 day course
Version	Date	Location	Places available	Book	Card	Invoice
2007	2009 Jan 8 Thu + 9 Fri	Bloomsbury	4	book now	£485	£490
2003	2009 Jan 22 Thu + 23 Fri	Southwark	1	book now	£495	£495
2003	2009 Jan 29 Thu + 30 Fri	Bayswater	4	book now	£475	£485
2007	2009 Feb 5 Thu + 6 Fri	Bloomsbury	5	book now	£485	£490
2003	2009 Feb 19 Thu + 20 Fri	Southwark	6	book now	£450	£485
2003	2009 Feb 26 Thu + 27 Fri	Bayswater	8	book now	£335	£365
Full Schedule: See all 44 Excel VBA course dates. Bookings currently available until 18th March 2010.

RE: sum through worksheets

by - trainer Anthony [5 posts] (2007 Nov 8 Thu, 14:57) Edited on 2007 Nov 8 Thu, 14:58 Reply

Hi Dawn:

You can use the SUM() function, but instead of summing a range of cells on the same sheet, you sum the same cell on a range of sheets. The formula is of the form:

=SUM('Sheet1:Sheet6'!A1)

To avoid typing, you can use the mouse to build up the formula as follows:

1. Select the cell where you want the result to be.
2. Click the AutoSum button, or type =SUM(
3. Click the TAB of the first sheet to be included in the sum.
4. Hold down SHIFT and click the TAB of the final sheet to be included. (The sheet tabs will turn white)
5. Release SHIFT and select the CELL to be summed.
6. Press ENTER before clicking back on the sheet you started from.

Once the correct formula is in one cell it can be copied to adjacent cells with Autofill.

Regards,

Tony Gay

· Reporting and Analysis with Excel
· An Introduction to Microsoft Excel: Top Features for the Excel Novice
· How Small Businesses Profit from Outside Excel Training
· 3 Ways that MS Excel Courses Can Drastically Improve Productivity for Teachers
· 3 Reasons Why Excel Training Courses For Employees Can Improve Your Business

Excel tip:

Select only cell that contain text to lock format

For selecting cells that only contain Text in Excel

By selecting cells that only contain text, you can delete, fill or protect cells of this type.

Use short cut to Go to box (F5) or Edit, Go to
In the dialog box, click special button & select Constants and only check text or any other desired type.

Click OK.

And text cells will all be highlighted for you to apply format. Please note only works on one sheet at a time.

View all Excel hints and tips

Rate this page: