Home |
Search |
Today's Posts |
|
#1
|
|||
|
|||
Calculating across worksheets
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 |
#2
|
|||
|
|||
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 "Steiner" wrote: 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 |
#3
|
|||
|
|||
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 "aristotle" wrote: 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 "Steiner" wrote: 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 |
#4
|
|||
|
|||
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" wrote in message ... 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 "aristotle" wrote: 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 "Steiner" wrote: 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 |
#5
|
|||
|
|||
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. Bob Phillips wrote: 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" wrote in message ... 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 "aristotle" wrote: 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 "Steiner" wrote: 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 -- Dave Peterson |
#6
|
|||
|
|||
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 "Steiner" wrote: 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 "aristotle" wrote: 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 "Steiner" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Adding multiple worksheets | Excel Worksheet Functions | |||
Define list of worksheets | Excel Worksheet Functions | |||
calculating excel spreadsheet files for pensions and life insurance (including age calculation sheets) | Excel Worksheet Functions | |||
AGE CALCULATING EXCEL SPREADSHEETS AND OTHERS! | Excel Discussion (Misc queries) | |||
HELP! How do you--> Lock a set of rows but also link worksheets to | Excel Discussion (Misc queries) |