Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Allewyn
 
Posts: n/a
Default Daily Totals on a summary sheet

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 doesnt 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?

  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Gord Dibben
 
Posts: n/a
Default Daily Totals on a summary sheet

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"
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?

  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Allewyn
 
Posts: n/a
Default Daily Totals on a summary sheet

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"
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 doesnt 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?


  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Allewyn
 
Posts: n/a
Default Daily Totals on a summary sheet

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"
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 doesnt 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?


  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Gord Dibben
 
Posts: n/a
Default Daily Totals on a summary sheet

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"
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"
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?




  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Allewyn
 
Posts: n/a
Default Daily Totals on a summary sheet

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"
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"
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 doesnt 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?


  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Gord Dibben
 
Posts: n/a
Default Daily Totals on a summary sheet

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"
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"
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"
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?


  #8   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Allewyn
 
Posts: n/a
Default Daily Totals on a summary sheet

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"
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"
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"
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 doesnt 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?



  #9   Report Post  
Posted to microsoft.public.excel.worksheet.functions
DKennedy
 
Posts: n/a
Default Daily Totals on a summary sheet

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 doesnt 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?

  #10   Report Post  
Posted to microsoft.public.excel.worksheet.functions
darkwood
 
Posts: n/a
Default Daily Totals on a summary sheet


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



  #11   Report Post  
Posted to microsoft.public.excel.worksheet.functions
JOUIOUI
 
Posts: n/a
Default Daily Totals on a summary sheet

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


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Referencing cell in another sheet yields null? [email protected] Excel Worksheet Functions 4 November 18th 05 02:11 AM
How to add totals in one sheet to another sheet k.floyd Excel Discussion (Misc queries) 1 November 9th 05 05:11 PM
I need a daily sales sheet to pull from monthly figures sheet Draegen Excel Worksheet Functions 0 April 24th 05 05:58 AM
How do I show summary totals from a pivot table on a bar chart Colleen T Charts and Charting in Excel 5 January 22nd 05 02:41 AM
Display summary sheet with Now or Today data. Wayne New Users to Excel 5 December 29th 04 09:45 AM


All times are GMT +1. The time now is 10:14 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"