summary values across worksheets within the same workbook

I have a single workbook consisting of 53 sheets (one per week in the fiscal 
year). I have a summary sheet that I need to extract data from the same cell 
in every sheet, updated automatically when any sheet is updated.

eg, Each of the 53 worksheets within the workbook has a total (car mileage) 
in cell C34, which I need to summarise as a total in a cell on the summary 
worksheet, on the basis of cell C34 on Sheet1+Sheet2+Sheet3... +Sheet53.

What function do I need in the required cell on the Summary sheet, please, 
to meet my needs?

I can't seem able to do what I want (I'm a bit dumb on Excel beyond basic 
and simple boolean logic functions), but I am sure that my problem will be 
simple to most of you experts.

My apologies if this has already been covered, and that I failed to find it.

Many thanks and best regards,
Gordon
0
Utf
4/30/2010 10:09:01 PM
excel.worksheet.functions 4936 articles. 2 followers. Follow

2 Replies
1193 Views

Similar Articles

[PageSpeed] 23

Try this;

Assuming your summary worksheet is the very first, or very last sheet (all 
the sheets you want to pull data from have to be in a row)

In C34 on your summary sheet, enter and equals sign and the SUM command;
=sum(

then click the first or last of your data tabs; then press and hold the 
shift key, then click the furthest data tab on the opposite end. This will 
select all of those data tabs together. Click on cell that holds your data 
that should feed into C34 (we'll assume it is in C34 on each weekly sheet).

Close out the formula with a close parenthesis, and hit enter. The final 
formula should look something like:

=SUM(MyWeek1Sheet:MyWeek52Sheet!C34)

and it should give you the value for all the sheets that you had selected.

HTH,
Keith


"Gordon" wrote:

> I have a single workbook consisting of 53 sheets (one per week in the fiscal 
> year). I have a summary sheet that I need to extract data from the same cell 
> in every sheet, updated automatically when any sheet is updated.
> 
> eg, Each of the 53 worksheets within the workbook has a total (car mileage) 
> in cell C34, which I need to summarise as a total in a cell on the summary 
> worksheet, on the basis of cell C34 on Sheet1+Sheet2+Sheet3... +Sheet53.
> 
> What function do I need in the required cell on the Summary sheet, please, 
> to meet my needs?
> 
> I can't seem able to do what I want (I'm a bit dumb on Excel beyond basic 
> and simple boolean logic functions), but I am sure that my problem will be 
> simple to most of you experts.
> 
> My apologies if this has already been covered, and that I failed to find it.
> 
> Many thanks and best regards,
> Gordon
0
Utf
4/30/2010 10:24:01 PM
In cell C34 on your summary sheet you,
will enter =SUM(Sheet1:Sheet53!C34). The formula adds the contents of cell C34 on Sheet1 through Sheet53. In C34 type =SUM( to begin the function. Activate Sheet1 Press Shift and activate Sheet 53 to group the worksheets and specify the worksheet range in your formula. Release the Shift key. In the active worksheet, select C34 and press the ENTER key to complete the 3-D formula.
0
Ormond (2)
4/30/2010 10:40:23 PM
Reply:

Similar Artilces: