Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
the sum of cells of many sheets
I have approx 150 sheets named with my customers name. I want to create a
cell that shows the sum of C12 in each sheet. Each of my clients has a number in C12 that shows the "Hours Required". I am trying to obtain a total of all hours for all clients. ='Doe, John'!C12+'Smith, Jane'!C12 Works fine for a few sheets but what if I have 150 sheets? What is the formula? I have also tried =SUM('Doe, John'!C12:'Smith, Jane'!C12) This gives me "$VALUE!" |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
the sum of cells of many sheets
Jeff,
Here's how it would look for summing A1 in Sheet1 through Sheet3: =SUM(Sheet1:Sheet3!A1) From 2003 Help here's some instructions: 1.. Click the cell where you want to enter the function. 2.. Type = (equal sign), enter the name of the function, and then type an opening parenthesis. 3.. Click the tab for the first worksheet to be referenced. 4.. Hold down SHIFT and click the tab for the last worksheet to be referenced. 5.. Select the cell or range of cells to be referenced. 6.. Complete the formula, and press ENTER. hth, Doug Glancy "Jeff Klein" wrote in message ... I have approx 150 sheets named with my customers name. I want to create a cell that shows the sum of C12 in each sheet. Each of my clients has a number in C12 that shows the "Hours Required". I am trying to obtain a total of all hours for all clients. ='Doe, John'!C12+'Smith, Jane'!C12 Works fine for a few sheets but what if I have 150 sheets? What is the formula? I have also tried =SUM('Doe, John'!C12:'Smith, Jane'!C12) This gives me "$VALUE!" |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
the sum of cells of many sheets
Jeff -
From the Excel help item titled "About cell and range references" The 3-D reference style If you want to analyze data in the same cell or range of cells on multiple worksheets within the workbook, use a 3-D reference. A 3-D reference includes the cell or range reference, preceded by a range of worksheet names. Excel uses any worksheets stored between the starting and ending names of the reference. For example, =SUM(Sheet2:Sheet13!B5) adds all the values contained in cell B5 on all the worksheets between and including Sheet 2 and Sheet 13. a.. You can use 3-D references to refer to cells on other sheets, to define names, and to create formulas by using the following functions: SUM, AVERAGE, AVERAGEA, COUNT, COUNTA, MAX, MAXA, MIN, MINA, PRODUCT, STDEV, STDEVA, STDEVP, STDEVPA, VAR, VARA, VARP, and VARPA. b.. 3-D references cannot be used in array formulas. c.. 3-D references cannot be used with the intersection operator (a single space) or in formulas that use implicit intersection. How 3-D references change when you move, copy, insert, or delete worksheets The following examples explain what happens when you move, copy, insert, or delete worksheets that are included in a 3-D reference. The examples use the formula =SUM(Sheet2:Sheet6!A2:A5) to add cells A2 through A5 on worksheets 2 through 6. Insert or copy If you insert or copy sheets between Sheet2 and Sheet6 (the endpoints in this example), Microsoft Excel includes all values in cells A2 through A5 from the added sheets in the calculations. Delete If you delete sheets between Sheet2 and Sheet6, Excel removes their values from the calculation. Move If you move sheets from between Sheet2 and Sheet6 to a location outside the referenced sheet range, Excel removes their values from the calculation. Move an endpoint If you move Sheet2 or Sheet6 to another location in the same workbook, Excel adjusts the calculation to accommodate the new range of sheets between them. Delete an endpoint If you delete Sheet2 or Sheet6, Excel adjusts the calculation to accommodate the range of sheets between them. I'm not sure if the above works with renamed worksheets, i.e. Smith, Jane instead of Sheet23, but I suspect it will follow the order that the worksheets are displayed in the workbook... HTH, James Cox "Jeff Klein" wrote in message ... I have approx 150 sheets named with my customers name. I want to create a cell that shows the sum of C12 in each sheet. Each of my clients has a number in C12 that shows the "Hours Required". I am trying to obtain a total of all hours for all clients. ='Doe, John'!C12+'Smith, Jane'!C12 Works fine for a few sheets but what if I have 150 sheets? What is the formula? I have also tried =SUM('Doe, John'!C12:'Smith, Jane'!C12) This gives me "$VALUE!" |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how to sum up the c13 cells in all of the sheets in a workbook | Excel Discussion (Misc queries) | |||
add cells together from different sheets within a workbook | Excel Worksheet Functions | |||
please help with formula for same cells in different sheets | Excel Worksheet Functions | |||
Linking Cells from different sheets | Excel Discussion (Misc queries) | |||
cells and sheets, names | Excel Worksheet Functions |