Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Referencing cell in another sheet yields null? | Excel Worksheet Functions | |||
How to add totals in one sheet to another sheet | Excel Discussion (Misc queries) | |||
I need a daily sales sheet to pull from monthly figures sheet | Excel Worksheet Functions | |||
How do I show summary totals from a pivot table on a bar chart | Charts and Charting in Excel | |||
Display summary sheet with Now or Today data. | New Users to Excel |