calculate cells over multiple worksheets

M

mrsjoan

I have 4 worksheets in one book. Worksheet names are July, August,
September, Total. In cell F9 of each worksheet I have a dollar value I would
like to add and place in a cell in a 4th worksheet.
Example: July's F9 is $600; August's F9 is $200; September's F9 is $100.
Now I want $900 to show in F9 of the worksheet Total.

How do I do this. I am a novice so please break it down clearly. Your help
will be greatly appreciated
 
C

Corey

You could use 'Named Range' to add them.
Select the F9 c ell in the 1st sheet and select>Insert>Name>Define
Call it F9Sheet1
Do the same for all sheets, changing only the Sheet 1 to 2 etc.
On the Total WorkSheet, enter the Formula as'=F9Sheet1+F9Sheet2+F9Sheet3'


Corey....
 
M

mrsjoan

THANKS Corey!!! it worked beautifully

Corey said:
You could use 'Named Range' to add them.
Select the F9 c ell in the 1st sheet and select>Insert>Name>Define
Call it F9Sheet1
Do the same for all sheets, changing only the Sheet 1 to 2 etc.
On the Total WorkSheet, enter the Formula as'=F9Sheet1+F9Sheet2+F9Sheet3'


Corey....
Click to expand...
 
A

Ashish Mathur

Hi,

You may try either of the following:

1. Data > Consolidate
2. On the total sheet, write the following formula =sum(First sheet:Last
sheet!F9)

--
Regards,

Ashsih Mathur
Microsoft Excel MVP
www.ashishmathur.com
 
R

Roger Govier

Hi

Assuming the sheets are in the order you say
=SUM(July:September!F9)

Alternatively, create 2 new sheets and name them First and Last.
Drag them to positions where they surround the sheets you wish to Sum. e.g
First, July, August, September, Last, Total
=SUM(First:Last!F9)
This has the advantage that if you add more sheets, you can just move the
position of Last to encompass the extra sheets and the same formula will
still work.
 
Top