Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Another data across multiple worksheets question (ugh..)
Aright, here's my problem that I've already been working an hour on:
End Goal = being able to see current total of all sales on every worksheet in a single cell. I have 4 worksheets in my workbook - Jan-Mar'07, Apr-June'07, July-Sept.'07, Oct.-Nov'07 For example, in the Jan-Mar'07 worksheet, I have a January column, Feb. column, and March column. Each month has a tally at the bottom that adds in everyday the new numbers. Then, to the right, I have a separate cell that adds all the months together =SUM(B36+E36+H36) , this is in cell K35. Now, when I click on the second worksheet Apr-June'07 everything is the exact same, with different numbers each day and months of course. K35 is the total of 3 months in this worksheet as well. And so on for the following two worksheets. So, in a nutshell, I guess I'm asking how do I take the values of K35 on every sheet and put it in a new cell that gives me a running tally for the entire year (so I don't have to keep adding up the K35 cells every darn time I want to know what the yearly total is - most annoying at the end of the year when all 4 sheets are full) I thought it was =SUM(Sheet1:Sheet2:Sheet3:Sheet4!K35) but I keep getting an 'error in formula' message. Please help.. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Another data across multiple worksheets question (ugh..)
Just specify the first and last sheets. All sheets in between are
automatically included. =SUM(Sheet1:Sheet4!K35) Alternatively, set up two empty sheets named "First" and "Last" and place them at either end of the sheets you want added. Then you can move sheets in and out (from between these two sheets) to include/exclude them from your total. =SUM(First:Last!K35) "Rasman007" wrote: Aright, here's my problem that I've already been working an hour on: End Goal = being able to see current total of all sales on every worksheet in a single cell. I have 4 worksheets in my workbook - Jan-Mar'07, Apr-June'07, July-Sept.'07, Oct.-Nov'07 For example, in the Jan-Mar'07 worksheet, I have a January column, Feb. column, and March column. Each month has a tally at the bottom that adds in everyday the new numbers. Then, to the right, I have a separate cell that adds all the months together =SUM(B36+E36+H36) , this is in cell K35. Now, when I click on the second worksheet Apr-June'07 everything is the exact same, with different numbers each day and months of course. K35 is the total of 3 months in this worksheet as well. And so on for the following two worksheets. So, in a nutshell, I guess I'm asking how do I take the values of K35 on every sheet and put it in a new cell that gives me a running tally for the entire year (so I don't have to keep adding up the K35 cells every darn time I want to know what the yearly total is - most annoying at the end of the year when all 4 sheets are full) I thought it was =SUM(Sheet1:Sheet2:Sheet3:Sheet4!K35) but I keep getting an 'error in formula' message. Please help.. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Another data across multiple worksheets question (ugh..)
Couple of solutions for you to consider:
='Jan-Mar''07'!K35 + 'Apr-June''07'!K35 + 'July-Sept.''07'!K35 + 'Oct.-Nov''07'!K35 or =sum('Jan-Mar''07:Oct.-Nov"'07'!K35) note the use of 2 ' in the name as you use ' in the name of the sheets. If you move the sheets around you need to becareful of the links and sometimes it is easier if you use the sum to but a blank sheet before the first and after the last one and do the sum inbetween those as in =sum(dummystart:dummyend!K35) then you can move the sheets around in between and not cause any problems. -- Hope this helps Martin Fishlock, Bangkok, Thailand Please do not forget to rate this reply. "Rasman007" wrote: Aright, here's my problem that I've already been working an hour on: End Goal = being able to see current total of all sales on every worksheet in a single cell. I have 4 worksheets in my workbook - Jan-Mar'07, Apr-June'07, July-Sept.'07, Oct.-Nov'07 For example, in the Jan-Mar'07 worksheet, I have a January column, Feb. column, and March column. Each month has a tally at the bottom that adds in everyday the new numbers. Then, to the right, I have a separate cell that adds all the months together =SUM(B36+E36+H36) , this is in cell K35. Now, when I click on the second worksheet Apr-June'07 everything is the exact same, with different numbers each day and months of course. K35 is the total of 3 months in this worksheet as well. And so on for the following two worksheets. So, in a nutshell, I guess I'm asking how do I take the values of K35 on every sheet and put it in a new cell that gives me a running tally for the entire year (so I don't have to keep adding up the K35 cells every darn time I want to know what the yearly total is - most annoying at the end of the year when all 4 sheets are full) I thought it was =SUM(Sheet1:Sheet2:Sheet3:Sheet4!K35) but I keep getting an 'error in formula' message. Please help.. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Manual control of link updating for downloaded quotes? | Excel Discussion (Misc queries) | |||
How to summarise data in the same place in multiple worksheets? | Excel Discussion (Misc queries) | |||
sort data on multiple worksheets | Excel Discussion (Misc queries) | |||
How to extract data from multiple worksheets.. | Excel Discussion (Misc queries) | |||
Extracting data from multiple worksheets into a list | Excel Worksheet Functions |