Calculating across worksheets

S

Steiner

Dear all,
I have 19 identical worksheets with information from different countries.
I would like to know all of these countries are doing as a whole. Using a
new worksheet that has an identical format to the other worksheets I would
like to calculate the sum of all the different countries from the other
sheets. Hope this is clear... Now do i have to like the cells of every
sheet individually or can I highlight all the sheets and as they are all
identical pick the cell which i would like the sum of. I have alread tried
the latter with no success and was wondering whether there is a method which
i did not know about or whether I do indeed need to go through each template
 
A

aristotle

Hi,

You could do something like =SUM(Sheet2!:Sheet10!A1), which is a process of
initiating the function, and once you've opened the parenthesis you group the
sheets and select the cell to sum.

Regards,
A
 
S

Steiner

Thank you
so by using =SUM(Sheet2!:Sheet10!A1) it is going to select all the sheets
between 2 and 10 for example. sorry to ask this but i dont have the template
with me at the moment
best regards,
laurent
 
B

Bob Phillips

Steiner,

Indeed that is what will happen.

Another trick you might want to implement is to create a sheet called
'First' and insert that before Sheet2, and a sheet called 'Last' after the
final sheet. These will just be placeholder sheets, not for data, so hide
them both and adjust the formula to =SUM(First!:Last!A1). Adding new sheets
will mean that they get automatically included.

--
HTH

Bob Phillips

Steiner said:
Thank you
so by using =SUM(Sheet2!:Sheet10!A1) it is going to select all the sheets
between 2 and 10 for example. sorry to ask this but i dont have the template
with me at the moment
best regards,
laurent
Click to expand...
template
 
A

aristotle

Hi,

Yes exactly, or it could even be something like =SUM(Australia!:China!A1),
and it would still select all sheets from Australia : China, picking up and
adding A1 for each of these sheets and all of those between.

Regards,
A
 
D

Dave Peterson

Just a typo alert:

=SUM(First!:Last!A1)
should be:
=SUM(First:Last!A1)

and a small difference in style...

I like to keep those sheets visible, but protected.

Then I can drag worksheets into or out of that "sandwich" and the formulas
adjust to what is currently between them.
 
Top