Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sum accross sheets
Hello
I know that as a worksheet function I can sum a cell on each sheet using =Sum(Sheet1!D4,Sheet2!D4) etc... However, programmatically, it's bound to be unnecessary. Im creating a summary sheet where it summarizes each month's value from a particular cell (all cells across the sheets are the same cell). EX: In each sheet (Named January to December), in Cell B4:D4 I have values. Range B4:B10 have values, C4:C10 have values, D4:D10 have values (on all sheets). On my summary sheet, I need to sum all the B4's into 1 cell, All the C4's in 1 cell etc etc etc. In essence: Sum(January:December!B4) in Cell B4 on the summary sheet, Sum(January:December!C4) etc etc. How can I do this? Thank you so much Terry V |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sum accross sheets
Terry,
Just put the formula in B4, drag and copy down to B10, and then drag and copy across to D10. -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Terry VanDuzee" wrote in message ... Hello I know that as a worksheet function I can sum a cell on each sheet using =Sum(Sheet1!D4,Sheet2!D4) etc... However, programmatically, it's bound to be unnecessary. Im creating a summary sheet where it summarizes each month's value from a particular cell (all cells across the sheets are the same cell). EX: In each sheet (Named January to December), in Cell B4:D4 I have values. Range B4:B10 have values, C4:C10 have values, D4:D10 have values (on all sheets). On my summary sheet, I need to sum all the B4's into 1 cell, All the C4's in 1 cell etc etc etc. In essence: Sum(January:December!B4) in Cell B4 on the summary sheet, Sum(January:December!C4) etc etc. How can I do this? Thank you so much Terry V |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sum accross sheets
Terry
exactly as you describe: =SUM(Sheet2:Sheet13!C3) =SUM(January:December!C3) Type in =SUM( Select the first sheet (January) Shift and select the last sheet (December) Select the cell close the bracket press enter Drag across/down, whatever. Regards Trevor "Terry VanDuzee" wrote in message ... Hello I know that as a worksheet function I can sum a cell on each sheet using =Sum(Sheet1!D4,Sheet2!D4) etc... However, programmatically, it's bound to be unnecessary. Im creating a summary sheet where it summarizes each month's value from a particular cell (all cells across the sheets are the same cell). EX: In each sheet (Named January to December), in Cell B4:D4 I have values. Range B4:B10 have values, C4:C10 have values, D4:D10 have values (on all sheets). On my summary sheet, I need to sum all the B4's into 1 cell, All the C4's in 1 cell etc etc etc. In essence: Sum(January:December!B4) in Cell B4 on the summary sheet, Sum(January:December!C4) etc etc. How can I do this? Thank you so much Terry V |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sum accross sheets
rotflmao - In essence, EXACTLY as you have surmised except you missed the = sign
:-) =SUM(January:December!B4) Then just copy the cell and paste across the range you want the formulas in. -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 00/02/03 ---------------------------------------------------------------------------- It's easier to beg forgiveness than ask permission :-) ---------------------------------------------------------------------------- "Terry VanDuzee" wrote in message ... Hello I know that as a worksheet function I can sum a cell on each sheet using =Sum(Sheet1!D4,Sheet2!D4) etc... However, programmatically, it's bound to be unnecessary. Im creating a summary sheet where it summarizes each month's value from a particular cell (all cells across the sheets are the same cell). EX: In each sheet (Named January to December), in Cell B4:D4 I have values. Range B4:B10 have values, C4:C10 have values, D4:D10 have values (on all sheets). On my summary sheet, I need to sum all the B4's into 1 cell, All the C4's in 1 cell etc etc etc. In essence: Sum(January:December!B4) in Cell B4 on the summary sheet, Sum(January:December!C4) etc etc. How can I do this? Thank you so much Terry V --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.572 / Virus Database: 362 - Release Date: 27/01/2004 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sum accross sheets
Assuming that sheets Jan - Dec are sheets 1 - 12 and that your total
will be on sheet 13: For x = 2 To 4 For y = 4 To 10 For z = 1 To 12 num1 = num1 + Worksheets(z).Cells(x, y).Value Next z Worksheets(13).Cells(x, y).Value = num1 num1 = 0 Next y Next x Let me know how that workd out. - Pikus --- Message posted from http://www.ExcelForum.com/ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
referencing specific data accross many sheets in a final sheet cha | Excel Discussion (Misc queries) | |||
adding accross sheets in a workbook | Excel Worksheet Functions | |||
Summing accross sheets | Excel Worksheet Functions | |||
Sumproduct accross multiple sheets | Excel Discussion (Misc queries) | |||
added line won't update accross sheets | Excel Discussion (Misc queries) |