| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
calculate totals from added sheets
pls help again
i have a petty cash file, where i open a new sheet for every day from a template sheet. how do i keep running totals of 10 data ranges in every sheet in one sheet - bearing in mind that every day a new sheet gets added? thanks |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
calculate totals from added sheets
Before you first daily sheet add a blank sheet and call it MyFirst
After you last daily sheet add a blank sheet and call it MyLast Always add new sheets in front of MyLast On the summary sheet (that should be outside the MyFirst:MyLast group use the formula =MyFirst:MyLast!A20 to add all the A20 cells in all the daily sheets best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "des-sa" wrote in message ... pls help again i have a petty cash file, where i open a new sheet for every day from a template sheet. how do i keep running totals of 10 data ranges in every sheet in one sheet - bearing in mind that every day a new sheet gets added? thanks |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
calculate totals from added sheets
bernard,
thanks for reply. i did exactly as you said: named 2 sheets "MyFirst" and "MyLast", inserted a new sheet "Sum" right at the beginning, before "MyFirst". left sheet "MASTER" in the middle of "MyFirst" & "MyLast". entered the formula you gave in cell A2 of sheet "Sum" (i changed cell A20 in your formula to B18 - which is my relevant one. the return i get is "#REF!". where have i gone wrong? thanks "Bernard Liengme" wrote: Before you first daily sheet add a blank sheet and call it MyFirst After you last daily sheet add a blank sheet and call it MyLast Always add new sheets in front of MyLast On the summary sheet (that should be outside the MyFirst:MyLast group use the formula =MyFirst:MyLast!A20 to add all the A20 cells in all the daily sheets best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "des-sa" wrote in message ... pls help again i have a petty cash file, where i open a new sheet for every day from a template sheet. how do i keep running totals of 10 data ranges in every sheet in one sheet - bearing in mind that every day a new sheet gets added? thanks |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
calculate totals from added sheets
Your sheets should be set up in the order as follows:
Master MyFirst Sheet1 Sheet2 Sheet3 ... Sheet10 MyLast Then on the Master sheet, where you want the total to appear, you would type the following formula: =SUM(MyFirst:MyLast!$B$18) -- John C "des-sa" wrote: bernard, thanks for reply. i did exactly as you said: named 2 sheets "MyFirst" and "MyLast", inserted a new sheet "Sum" right at the beginning, before "MyFirst". left sheet "MASTER" in the middle of "MyFirst" & "MyLast". entered the formula you gave in cell A2 of sheet "Sum" (i changed cell A20 in your formula to B18 - which is my relevant one. the return i get is "#REF!". where have i gone wrong? thanks "Bernard Liengme" wrote: Before you first daily sheet add a blank sheet and call it MyFirst After you last daily sheet add a blank sheet and call it MyLast Always add new sheets in front of MyLast On the summary sheet (that should be outside the MyFirst:MyLast group use the formula =MyFirst:MyLast!A20 to add all the A20 cells in all the daily sheets best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "des-sa" wrote in message ... pls help again i have a petty cash file, where i open a new sheet for every day from a template sheet. how do i keep running totals of 10 data ranges in every sheet in one sheet - bearing in mind that every day a new sheet gets added? thanks |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
calculate totals from added sheets
Thanks, John. I left out SUM
The 34C (95F0 temp is getting to me here in the far north best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "John C" <johnc@stateofdenial wrote in message ... Your sheets should be set up in the order as follows: Master MyFirst Sheet1 Sheet2 Sheet3 ... Sheet10 MyLast Then on the Master sheet, where you want the total to appear, you would type the following formula: =SUM(MyFirst:MyLast!$B$18) -- John C "des-sa" wrote: bernard, thanks for reply. i did exactly as you said: named 2 sheets "MyFirst" and "MyLast", inserted a new sheet "Sum" right at the beginning, before "MyFirst". left sheet "MASTER" in the middle of "MyFirst" & "MyLast". entered the formula you gave in cell A2 of sheet "Sum" (i changed cell A20 in your formula to B18 - which is my relevant one. the return i get is "#REF!". where have i gone wrong? thanks "Bernard Liengme" wrote: Before you first daily sheet add a blank sheet and call it MyFirst After you last daily sheet add a blank sheet and call it MyLast Always add new sheets in front of MyLast On the summary sheet (that should be outside the MyFirst:MyLast group use the formula =MyFirst:MyLast!A20 to add all the A20 cells in all the daily sheets best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "des-sa" wrote in message ... pls help again i have a petty cash file, where i open a new sheet for every day from a template sheet. how do i keep running totals of 10 data ranges in every sheet in one sheet - bearing in mind that every day a new sheet gets added? thanks |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
calculate totals from added sheets
john,
thanks for your reply. sheet "master" is actually the template that i use to copy as the new day's sheet with a macro i created. roughly explained, the macro opens a copy of sheet "master", clears the entries in the previous day's cell ranges where data have been entered into (not the cells where totals are calculated). how do i force the opening of the new sheet every day to be after the previous day's sheet? or can you may suggest a simpler way of doing it? thanks for your patience with me "John C" wrote: Your sheets should be set up in the order as follows: Master MyFirst Sheet1 Sheet2 Sheet3 ... Sheet10 MyLast Then on the Master sheet, where you want the total to appear, you would type the following formula: =SUM(MyFirst:MyLast!$B$18) -- John C "des-sa" wrote: bernard, thanks for reply. i did exactly as you said: named 2 sheets "MyFirst" and "MyLast", inserted a new sheet "Sum" right at the beginning, before "MyFirst". left sheet "MASTER" in the middle of "MyFirst" & "MyLast". entered the formula you gave in cell A2 of sheet "Sum" (i changed cell A20 in your formula to B18 - which is my relevant one. the return i get is "#REF!". where have i gone wrong? thanks "Bernard Liengme" wrote: Before you first daily sheet add a blank sheet and call it MyFirst After you last daily sheet add a blank sheet and call it MyLast Always add new sheets in front of MyLast On the summary sheet (that should be outside the MyFirst:MyLast group use the formula =MyFirst:MyLast!A20 to add all the A20 cells in all the daily sheets best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "des-sa" wrote in message ... pls help again i have a petty cash file, where i open a new sheet for every day from a template sheet. how do i keep running totals of 10 data ranges in every sheet in one sheet - bearing in mind that every day a new sheet gets added? thanks |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
calculate totals from added sheets
This should be part of your macrocode:
Sheets("Master").Select The line after that, you would have the following: Sheets("Master").Copy Befo=Sheets("MyLast") -- John C "des-sa" wrote: john, thanks for your reply. sheet "master" is actually the template that i use to copy as the new day's sheet with a macro i created. roughly explained, the macro opens a copy of sheet "master", clears the entries in the previous day's cell ranges where data have been entered into (not the cells where totals are calculated). how do i force the opening of the new sheet every day to be after the previous day's sheet? or can you may suggest a simpler way of doing it? thanks for your patience with me "John C" wrote: Your sheets should be set up in the order as follows: Master MyFirst Sheet1 Sheet2 Sheet3 ... Sheet10 MyLast Then on the Master sheet, where you want the total to appear, you would type the following formula: =SUM(MyFirst:MyLast!$B$18) -- John C "des-sa" wrote: bernard, thanks for reply. i did exactly as you said: named 2 sheets "MyFirst" and "MyLast", inserted a new sheet "Sum" right at the beginning, before "MyFirst". left sheet "MASTER" in the middle of "MyFirst" & "MyLast". entered the formula you gave in cell A2 of sheet "Sum" (i changed cell A20 in your formula to B18 - which is my relevant one. the return i get is "#REF!". where have i gone wrong? thanks "Bernard Liengme" wrote: Before you first daily sheet add a blank sheet and call it MyFirst After you last daily sheet add a blank sheet and call it MyLast Always add new sheets in front of MyLast On the summary sheet (that should be outside the MyFirst:MyLast group use the formula =MyFirst:MyLast!A20 to add all the A20 cells in all the daily sheets best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "des-sa" wrote in message ... pls help again i have a petty cash file, where i open a new sheet for every day from a template sheet. how do i keep running totals of 10 data ranges in every sheet in one sheet - bearing in mind that every day a new sheet gets added? thanks |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
calculate totals from added sheets
JOHN & BERNARD
I GOT SO CHUFFEDLY MIXED UP IN MY WORK I FORGOT TO THANK YOU GUYS. THANK YOU SO MUCH GUYS DISRI "Bernard Liengme" wrote: Thanks, John. I left out SUM The 34C (95F0 temp is getting to me here in the far north best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "John C" <johnc@stateofdenial wrote in message ... Your sheets should be set up in the order as follows: Master MyFirst Sheet1 Sheet2 Sheet3 ... Sheet10 MyLast Then on the Master sheet, where you want the total to appear, you would type the following formula: =SUM(MyFirst:MyLast!$B$18) -- John C "des-sa" wrote: bernard, thanks for reply. i did exactly as you said: named 2 sheets "MyFirst" and "MyLast", inserted a new sheet "Sum" right at the beginning, before "MyFirst". left sheet "MASTER" in the middle of "MyFirst" & "MyLast". entered the formula you gave in cell A2 of sheet "Sum" (i changed cell A20 in your formula to B18 - which is my relevant one. the return i get is "#REF!". where have i gone wrong? thanks "Bernard Liengme" wrote: Before you first daily sheet add a blank sheet and call it MyFirst After you last daily sheet add a blank sheet and call it MyLast Always add new sheets in front of MyLast On the summary sheet (that should be outside the MyFirst:MyLast group use the formula =MyFirst:MyLast!A20 to add all the A20 cells in all the daily sheets best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "des-sa" wrote in message ... pls help again i have a petty cash file, where i open a new sheet for every day from a template sheet. how do i keep running totals of 10 data ranges in every sheet in one sheet - bearing in mind that every day a new sheet gets added? thanks |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Color fill cells to be added into totals | Excel Worksheet Functions | |||
| Calculate totals | Excel Discussion (Misc queries) | |||
| added line won't update accross sheets | Excel Discussion (Misc queries) | |||
| how do i get the name of a newly added sheet that i add with Sheets.Add in a vba macro in excel? | Excel Worksheet Functions | |||
| calculate totals of 5 sheets on to 6th sheet. | Excel Worksheet Functions | |||
![des-sa[_2_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Linear Mode
