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

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

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

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

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

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

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

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