Daily Totals on a summary sheet

[Allewyn]

I have 14 sheets in the workbook
Each sheet has names and numbers and a ‘total calls made’ cell which I call
from a separate summary sheet
I am tracking calls everyday on the 14 different sheets (different categories)
Yesterday I went through the pain of typing =sum(, click sheet tab, select
cell, +, click next sheet tab, select cell, +, etc.
Yesterday I made 16 calls and the above approach worked great
Today I made 3 calls
Now it doesn’t work as great because I want to keep the daily totals
separate on the summary sheet without having to do what I did yesterday.

Column
J2
Row A2 16 Calls yesterday display in A2
3 Calls today
currently 19 displayed on summary sheet from yesterday in
row A2 again, wiping out the prior
number 16
would like to display 3 on summary sheet in a different cell from
the 16 prior calls, such as A4, without having
to go through typing (+, clicking
sheet tab, select cell), (+ clicking
sheet tab, select cell), etc every day.

Is there a way to do this with an automatic update from each sheet to the
summary sheet?

[Gord Dibben]

Allewyn

If the total cell on each sheet is same cell you can use

=SUM(Sheet1:Sheet23!cellref)

To allow for inserting more sheets later, I recommend inserting a dummy sheet
named Start at first sheet in book.

Insert a dummy sheet named End at last sheet in book.

Then formula can be =SUM(Start:End!cellref)

New sheets will be inserted between Start and End sheets.


Gord Dibben Excel MVP

On Wed, 21 Dec 2005 09:11:03 -0800, "Allewyn"
<[email protected]> wrote:

>I have 14 sheets in the workbook
>Each sheet has names and numbers and a ‘total calls made’ cell which I call
>from a separate summary sheet
>I am tracking calls everyday on the 14 different sheets (different categories)
>Yesterday I went through the pain of typing =sum(, click sheet tab, select
>cell, +, click next sheet tab, select cell, +, etc.
>Yesterday I made 16 calls and the above approach worked great
>Today I made 3 calls
>Now it doesn’t work as great because I want to keep the daily totals
>separate on the summary sheet without having to do what I did yesterday.
>
> Column
> J2
>Row A2 16 Calls yesterday display in A2
> 3 Calls today
> currently 19 displayed on summary sheet from yesterday in
> row A2 again, wiping out the prior
>number 16
>would like to display 3 on summary sheet in a different cell from
> the 16 prior calls, such as A4, without having
>to go through typing (+, clicking
>sheet tab, select cell), (+ clicking
>sheet tab, select cell), etc every day.
>
>Is there a way to do this with an automatic update from each sheet to the
>summary sheet?

[Allewyn]

Ahhh, that's pretty slick! Thanks for the help

"Gord Dibben" wrote:

> Allewyn
>
> If the total cell on each sheet is same cell you can use
>
> =SUM(Sheet1:Sheet23!cellref)
>
> To allow for inserting more sheets later, I recommend inserting a dummy sheet
> named Start at first sheet in book.
>
> Insert a dummy sheet named End at last sheet in book.
>
> Then formula can be =SUM(Start:End!cellref)
>
> New sheets will be inserted between Start and End sheets.
>
>
> Gord Dibben Excel MVP
>
> On Wed, 21 Dec 2005 09:11:03 -0800, "Allewyn"
> <[email protected]> wrote:
>
> >I have 14 sheets in the workbook
> >Each sheet has names and numbers and a ‘total calls made’ cell which I call
> >from a separate summary sheet
> >I am tracking calls everyday on the 14 different sheets (different categories)
> >Yesterday I went through the pain of typing =sum(, click sheet tab, select
> >cell, +, click next sheet tab, select cell, +, etc.
> >Yesterday I made 16 calls and the above approach worked great
> >Today I made 3 calls
> >Now it doesn’t work as great because I want to keep the daily totals
> >separate on the summary sheet without having to do what I did yesterday.
> >
> > Column
> > J2
> >Row A2 16 Calls yesterday display in A2
> > 3 Calls today
> > currently 19 displayed on summary sheet from yesterday in
> > row A2 again, wiping out the prior
> >number 16
> >would like to display 3 on summary sheet in a different cell from
> > the 16 prior calls, such as A4, without having
> >to go through typing (+, clicking
> >sheet tab, select cell), (+ clicking
> >sheet tab, select cell), etc every day.
> >
> >Is there a way to do this with an automatic update from each sheet to the
> >summary sheet?
>

[Allewyn]

Does it matter if the sheets between Start and End are named other than
sheet2, sheet3, etc? Mine are and I get the dreaded #VALUE? uisng the
=sum(Start:End!A1) method. Thought I understood. Am I supposed to include
the characters "cellref" in there?

The other thing is, if this does work (which I'm sure it will when I get it
right) how does it address the need for daily results getting displayed in
different cells on my summary page?

"Gord Dibben" wrote:

> Allewyn
>
> If the total cell on each sheet is same cell you can use
>
> =SUM(Sheet1:Sheet23!cellref)
>
> To allow for inserting more sheets later, I recommend inserting a dummy sheet
> named Start at first sheet in book.
>
> Insert a dummy sheet named End at last sheet in book.
>
> Then formula can be =SUM(Start:End!cellref)
>
> New sheets will be inserted between Start and End sheets.
>
>
> Gord Dibben Excel MVP
>
> On Wed, 21 Dec 2005 09:11:03 -0800, "Allewyn"
> <[email protected]> wrote:
>
> >I have 14 sheets in the workbook
> >Each sheet has names and numbers and a ‘total calls made’ cell which I call
> >from a separate summary sheet
> >I am tracking calls everyday on the 14 different sheets (different categories)
> >Yesterday I went through the pain of typing =sum(, click sheet tab, select
> >cell, +, click next sheet tab, select cell, +, etc.
> >Yesterday I made 16 calls and the above approach worked great
> >Today I made 3 calls
> >Now it doesn’t work as great because I want to keep the daily totals
> >separate on the summary sheet without having to do what I did yesterday.
> >
> > Column
> > J2
> >Row A2 16 Calls yesterday display in A2
> > 3 Calls today
> > currently 19 displayed on summary sheet from yesterday in
> > row A2 again, wiping out the prior
> >number 16
> >would like to display 3 on summary sheet in a different cell from
> > the 16 prior calls, such as A4, without having
> >to go through typing (+, clicking
> >sheet tab, select cell), (+ clicking
> >sheet tab, select cell), etc every day.
> >
> >Is there a way to do this with an automatic update from each sheet to the
> >summary sheet?
>

[Gord Dibben]

Allewyn

The sheets between Start and End can be named any way you want as long as it's
a legal name.

Instead of the word "cellref" you are to enter a specific cell address like A1
or E45

The values in any of those cells must be numeric or you will get the error.

This method is useful only when summing same cell on each sheet.

If cells to sum are spread about, you need to point to them cell by cell and
sheet by sheet in your SUM formula.


Gord

On Wed, 21 Dec 2005 14:35:07 -0800, "Allewyn"
<[email protected]> wrote:

>Does it matter if the sheets between Start and End are named other than
>sheet2, sheet3, etc? Mine are and I get the dreaded #VALUE? uisng the
>=sum(Start:End!A1) method. Thought I understood. Am I supposed to include
>the characters "cellref" in there?
>
>The other thing is, if this does work (which I'm sure it will when I get it
>right) how does it address the need for daily results getting displayed in
>different cells on my summary page?
>
>"Gord Dibben" wrote:
>
>> Allewyn
>>
>> If the total cell on each sheet is same cell you can use
>>
>> =SUM(Sheet1:Sheet23!cellref)
>>
>> To allow for inserting more sheets later, I recommend inserting a dummy sheet
>> named Start at first sheet in book.
>>
>> Insert a dummy sheet named End at last sheet in book.
>>
>> Then formula can be =SUM(Start:End!cellref)
>>
>> New sheets will be inserted between Start and End sheets.
>>
>>
>> Gord Dibben Excel MVP
>>
>> On Wed, 21 Dec 2005 09:11:03 -0800, "Allewyn"
>> <[email protected]> wrote:
>>
>> >I have 14 sheets in the workbook
>> >Each sheet has names and numbers and a ‘total calls made’ cell which I call
>> >from a separate summary sheet
>> >I am tracking calls everyday on the 14 different sheets (different categories)
>> >Yesterday I went through the pain of typing =sum(, click sheet tab, select
>> >cell, +, click next sheet tab, select cell, +, etc.
>> >Yesterday I made 16 calls and the above approach worked great
>> >Today I made 3 calls
>> >Now it doesn’t work as great because I want to keep the daily totals
>> >separate on the summary sheet without having to do what I did yesterday.
>> >
>> > Column
>> > J2
>> >Row A2 16 Calls yesterday display in A2
>> > 3 Calls today
>> > currently 19 displayed on summary sheet from yesterday in
>> > row A2 again, wiping out the prior
>> >number 16
>> >would like to display 3 on summary sheet in a different cell from
>> > the 16 prior calls, such as A4, without having
>> >to go through typing (+, clicking
>> >sheet tab, select cell), (+ clicking
>> >sheet tab, select cell), etc every day.
>> >
>> >Is there a way to do this with an automatic update from each sheet to the
>> >summary sheet?
>>

[Allewyn]

OK, thanks for the clarification. I have one last question: if the cells on
each sheet are the same and have numberical values, is there a reason I might
get the #value! error message? I've checked each cell to make sure the
formatting is "general", including the cell I'm calling from. Should I use
=sum(value(a1)) then CONTROL SHIFT ENTER to verify for this on each sheet?

I appreciate your help and wish you the merriest of Christmases!

"Gord Dibben" wrote:

> Allewyn
>
> The sheets between Start and End can be named any way you want as long as it's
> a legal name.
>
> Instead of the word "cellref" you are to enter a specific cell address like A1
> or E45
>
> The values in any of those cells must be numeric or you will get the error.
>
> This method is useful only when summing same cell on each sheet.
>
> If cells to sum are spread about, you need to point to them cell by cell and
> sheet by sheet in your SUM formula.
>
>
> Gord
>
> On Wed, 21 Dec 2005 14:35:07 -0800, "Allewyn"
> <[email protected]> wrote:
>
> >Does it matter if the sheets between Start and End are named other than
> >sheet2, sheet3, etc? Mine are and I get the dreaded #VALUE? uisng the
> >=sum(Start:End!A1) method. Thought I understood. Am I supposed to include
> >the characters "cellref" in there?
> >
> >The other thing is, if this does work (which I'm sure it will when I get it
> >right) how does it address the need for daily results getting displayed in
> >different cells on my summary page?
> >
> >"Gord Dibben" wrote:
> >
> >> Allewyn
> >>
> >> If the total cell on each sheet is same cell you can use
> >>
> >> =SUM(Sheet1:Sheet23!cellref)
> >>
> >> To allow for inserting more sheets later, I recommend inserting a dummy sheet
> >> named Start at first sheet in book.
> >>
> >> Insert a dummy sheet named End at last sheet in book.
> >>
> >> Then formula can be =SUM(Start:End!cellref)
> >>
> >> New sheets will be inserted between Start and End sheets.
> >>
> >>
> >> Gord Dibben Excel MVP
> >>
> >> On Wed, 21 Dec 2005 09:11:03 -0800, "Allewyn"
> >> <[email protected]> wrote:
> >>
> >> >I have 14 sheets in the workbook
> >> >Each sheet has names and numbers and a ‘total calls made’ cell which I call
> >> >from a separate summary sheet
> >> >I am tracking calls everyday on the 14 different sheets (different categories)
> >> >Yesterday I went through the pain of typing =sum(, click sheet tab, select
> >> >cell, +, click next sheet tab, select cell, +, etc.
> >> >Yesterday I made 16 calls and the above approach worked great
> >> >Today I made 3 calls
> >> >Now it doesn’t work as great because I want to keep the daily totals
> >> >separate on the summary sheet without having to do what I did yesterday.
> >> >
> >> > Column
> >> > J2
> >> >Row A2 16 Calls yesterday display in A2
> >> > 3 Calls today
> >> > currently 19 displayed on summary sheet from yesterday in
> >> > row A2 again, wiping out the prior
> >> >number 16
> >> >would like to display 3 on summary sheet in a different cell from
> >> > the 16 prior calls, such as A4, without having
> >> >to go through typing (+, clicking
> >> >sheet tab, select cell), (+ clicking
> >> >sheet tab, select cell), etc every day.
> >> >
> >> >Is there a way to do this with an automatic update from each sheet to the
> >> >summary sheet?
> >>
>

[Gord Dibben]

Allewyn

I told you an untruth.

Excel's SUM ignores non-numerics so you should not be getting the error even
if a cell on a sheet is non-numeric.

You must have a #value! error in one of the cells.

Caused by what is hard to tell from here.

Assuming you are summing A1 on each sheet, what is in A1?

Entered number? Number returned by formula?

If you want to send me a copy of the workbook, I will have a look.

Change the AT and DOT in my email address to @ and .


Gord Dibben Excel MVP

On Thu, 22 Dec 2005 08:32:03 -0800, "Allewyn"
<[email protected]> wrote:

>OK, thanks for the clarification. I have one last question: if the cells on
>each sheet are the same and have numberical values, is there a reason I might
>get the #value! error message? I've checked each cell to make sure the
>formatting is "general", including the cell I'm calling from. Should I use
>=sum(value(a1)) then CONTROL SHIFT ENTER to verify for this on each sheet?
>
>I appreciate your help and wish you the merriest of Christmases!
>
>"Gord Dibben" wrote:
>
>> Allewyn
>>
>> The sheets between Start and End can be named any way you want as long as it's
>> a legal name.
>>
>> Instead of the word "cellref" you are to enter a specific cell address like A1
>> or E45
>>
>> The values in any of those cells must be numeric or you will get the error.
>>
>> This method is useful only when summing same cell on each sheet.
>>
>> If cells to sum are spread about, you need to point to them cell by cell and
>> sheet by sheet in your SUM formula.
>>
>>
>> Gord
>>
>> On Wed, 21 Dec 2005 14:35:07 -0800, "Allewyn"
>> <[email protected]> wrote:
>>
>> >Does it matter if the sheets between Start and End are named other than
>> >sheet2, sheet3, etc? Mine are and I get the dreaded #VALUE? uisng the
>> >=sum(Start:End!A1) method. Thought I understood. Am I supposed to include
>> >the characters "cellref" in there?
>> >
>> >The other thing is, if this does work (which I'm sure it will when I get it
>> >right) how does it address the need for daily results getting displayed in
>> >different cells on my summary page?
>> >
>> >"Gord Dibben" wrote:
>> >
>> >> Allewyn
>> >>
>> >> If the total cell on each sheet is same cell you can use
>> >>
>> >> =SUM(Sheet1:Sheet23!cellref)
>> >>
>> >> To allow for inserting more sheets later, I recommend inserting a dummy sheet
>> >> named Start at first sheet in book.
>> >>
>> >> Insert a dummy sheet named End at last sheet in book.
>> >>
>> >> Then formula can be =SUM(Start:End!cellref)
>> >>
>> >> New sheets will be inserted between Start and End sheets.
>> >>
>> >>
>> >> Gord Dibben Excel MVP
>> >>
>> >> On Wed, 21 Dec 2005 09:11:03 -0800, "Allewyn"
>> >> <[email protected]> wrote:
>> >>
>> >> >I have 14 sheets in the workbook
>> >> >Each sheet has names and numbers and a ‘total calls made’ cell which I call
>> >> >from a separate summary sheet
>> >> >I am tracking calls everyday on the 14 different sheets (different categories)
>> >> >Yesterday I went through the pain of typing =sum(, click sheet tab, select
>> >> >cell, +, click next sheet tab, select cell, +, etc.
>> >> >Yesterday I made 16 calls and the above approach worked great
>> >> >Today I made 3 calls
>> >> >Now it doesn’t work as great because I want to keep the daily totals
>> >> >separate on the summary sheet without having to do what I did yesterday.
>> >> >
>> >> > Column
>> >> > J2
>> >> >Row A2 16 Calls yesterday display in A2
>> >> > 3 Calls today
>> >> > currently 19 displayed on summary sheet from yesterday in
>> >> > row A2 again, wiping out the prior
>> >> >number 16
>> >> >would like to display 3 on summary sheet in a different cell from
>> >> > the 16 prior calls, such as A4, without having
>> >> >to go through typing (+, clicking
>> >> >sheet tab, select cell), (+ clicking
>> >> >sheet tab, select cell), etc every day.
>> >> >
>> >> >Is there a way to do this with an automatic update from each sheet to the
>> >> >summary sheet?
>> >>
>>

[Allewyn]

That's ok, I kept working with what you gave me and worked it out myself.
Thanks for the offer I'm 100%

"Gord Dibben" wrote:

> Allewyn
>
> I told you an untruth.
>
> Excel's SUM ignores non-numerics so you should not be getting the error even
> if a cell on a sheet is non-numeric.
>
> You must have a #value! error in one of the cells.
>
> Caused by what is hard to tell from here.
>
> Assuming you are summing A1 on each sheet, what is in A1?
>
> Entered number? Number returned by formula?
>
> If you want to send me a copy of the workbook, I will have a look.
>
> Change the AT and DOT in my email address to @ and .
>
>
> Gord Dibben Excel MVP
>
> On Thu, 22 Dec 2005 08:32:03 -0800, "Allewyn"
> <[email protected]> wrote:
>
> >OK, thanks for the clarification. I have one last question: if the cells on
> >each sheet are the same and have numberical values, is there a reason I might
> >get the #value! error message? I've checked each cell to make sure the
> >formatting is "general", including the cell I'm calling from. Should I use
> >=sum(value(a1)) then CONTROL SHIFT ENTER to verify for this on each sheet?
> >
> >I appreciate your help and wish you the merriest of Christmases!
> >
> >"Gord Dibben" wrote:
> >
> >> Allewyn
> >>
> >> The sheets between Start and End can be named any way you want as long as it's
> >> a legal name.
> >>
> >> Instead of the word "cellref" you are to enter a specific cell address like A1
> >> or E45
> >>
> >> The values in any of those cells must be numeric or you will get the error.
> >>
> >> This method is useful only when summing same cell on each sheet.
> >>
> >> If cells to sum are spread about, you need to point to them cell by cell and
> >> sheet by sheet in your SUM formula.
> >>
> >>
> >> Gord
> >>
> >> On Wed, 21 Dec 2005 14:35:07 -0800, "Allewyn"
> >> <[email protected]> wrote:
> >>
> >> >Does it matter if the sheets between Start and End are named other than
> >> >sheet2, sheet3, etc? Mine are and I get the dreaded #VALUE? uisng the
> >> >=sum(Start:End!A1) method. Thought I understood. Am I supposed to include
> >> >the characters "cellref" in there?
> >> >
> >> >The other thing is, if this does work (which I'm sure it will when I get it
> >> >right) how does it address the need for daily results getting displayed in
> >> >different cells on my summary page?
> >> >
> >> >"Gord Dibben" wrote:
> >> >
> >> >> Allewyn
> >> >>
> >> >> If the total cell on each sheet is same cell you can use
> >> >>
> >> >> =SUM(Sheet1:Sheet23!cellref)
> >> >>
> >> >> To allow for inserting more sheets later, I recommend inserting a dummy sheet
> >> >> named Start at first sheet in book.
> >> >>
> >> >> Insert a dummy sheet named End at last sheet in book.
> >> >>
> >> >> Then formula can be =SUM(Start:End!cellref)
> >> >>
> >> >> New sheets will be inserted between Start and End sheets.
> >> >>
> >> >>
> >> >> Gord Dibben Excel MVP
> >> >>
> >> >> On Wed, 21 Dec 2005 09:11:03 -0800, "Allewyn"
> >> >> <[email protected]> wrote:
> >> >>
> >> >> >I have 14 sheets in the workbook
> >> >> >Each sheet has names and numbers and a ‘total calls made’ cell which I call
> >> >> >from a separate summary sheet
> >> >> >I am tracking calls everyday on the 14 different sheets (different categories)
> >> >> >Yesterday I went through the pain of typing =sum(, click sheet tab, select
> >> >> >cell, +, click next sheet tab, select cell, +, etc.
> >> >> >Yesterday I made 16 calls and the above approach worked great
> >> >> >Today I made 3 calls
> >> >> >Now it doesn’t work as great because I want to keep the daily totals
> >> >> >separate on the summary sheet without having to do what I did yesterday.
> >> >> >
> >> >> > Column
> >> >> > J2
> >> >> >Row A2 16 Calls yesterday display in A2
> >> >> > 3 Calls today
> >> >> > currently 19 displayed on summary sheet from yesterday in
> >> >> > row A2 again, wiping out the prior
> >> >> >number 16
> >> >> >would like to display 3 on summary sheet in a different cell from
> >> >> > the 16 prior calls, such as A4, without having
> >> >> >to go through typing (+, clicking
> >> >> >sheet tab, select cell), (+ clicking
> >> >> >sheet tab, select cell), etc every day.
> >> >> >
> >> >> >Is there a way to do this with an automatic update from each sheet to the
> >> >> >summary sheet?
> >> >>
> >>
>

[DKennedy]

I have a similar problem. I want to capture daily totals to get a daily
history in order to track the progress of a project. I have a summary sheet
that keeps a running total of the progress made from 4 other sheets. I have
three cell that list dates, the start date of the project, the end date and
todays date using =now()
A B C
1 Project Name % of Budget %completed
2 Name 20% 25% (this is a running total)
3 Today
4 Start
5 End
6
7 start day 5% 5%(these are daily running totals)
8 day1 6% 9%
9 day2 14% 17%
10 End day 20% 25%

Currently I would have to cut and paste each days activity into A7,A8...

Were you able to solve your problem and do you have any insight into mine?



"Allewyn" wrote:

> I have 14 sheets in the workbook
> Each sheet has names and numbers and a ‘total calls made’ cell which I call
> from a separate summary sheet
> I am tracking calls everyday on the 14 different sheets (different categories)
> Yesterday I went through the pain of typing =sum(, click sheet tab, select
> cell, +, click next sheet tab, select cell, +, etc.
> Yesterday I made 16 calls and the above approach worked great
> Today I made 3 calls
> Now it doesn’t work as great because I want to keep the daily totals
> separate on the summary sheet without having to do what I did yesterday.
>
> Column
> J2
> Row A2 16 Calls yesterday display in A2
> 3 Calls today
> currently 19 displayed on summary sheet from yesterday in
> row A2 again, wiping out the prior
> number 16
> would like to display 3 on summary sheet in a different cell from
> the 16 prior calls, such as A4, without having
> to go through typing (+, clicking
> sheet tab, select cell), (+ clicking
> sheet tab, select cell), etc every day.
>
> Is there a way to do this with an automatic update from each sheet to the
> summary sheet?
>

[darkwood]

Assuming that your cell reference is static on all sheets (meaning same cell location Ex: B214), I don't see how this formula wouldn't work. If they aren't static, can they be formatted to be?

[JOUIOUI]

I was searching previous posts before posting my question and this post sort
of fits my situation except for the fact that my cell location on my
differnet sheets in my workbook are never the same. How would I summerize my
totals from my differents sheets onto the summary sheet

For example

Sheet titled "cards" has Sum in Column D, row 56
Sheet titled "checks" has sum in Column D row 66
Sheet Titled "cash" has sum in column D row 156.

The only common thread is the total on each sheet is the last text in Column
D on that sheet. Also the row number varies each day so my question is how
would I code to copy the last cell in Column D on the sheet titled Cards with
a sum total to sheet titled Summary into Cell B4, the same for sheet Checks
copy to cell B5 in Summary sheet and Cash to cell B6. Is this even possible.
Thanks
"darkwood" wrote:

>
> Assuming that your cell reference is static on all sheets (meaning same
> cell location Ex: B214), I don't see how this formula wouldn't work.
> If they aren't static, can they be formatted to be?
>
>
> --
> darkwood
> ------------------------------------------------------------------------
> darkwood's Profile: http://www.excelforum.com/member.php...o&userid=29948
> View this thread: http://www.excelforum.com/showthread...hreadid=495242
>
>