Recalc of SUM() that refers to a cell across a range of worksheets

I'm using Excel 2003 w/ SP2 and have encountered a problem I don't recall 
having before.  I have a workbook where a summary sheet is used to aggregate 
the values in each of the cells in the rest of the sheets by using a formula 
with following syntax:
=sum('sheet1:sheetx'!Cn),
where "sheet1" is the name of the first sheet in the range of contiguous 
sheets, "sheetx" is the name of the last sheet in that range, "C" is the 
pertinent column letter for a cell (the columns in this workbook represent 
years) and "n" is the pertinent row number.

When I insert a new column (for a new year) in the supporting worksheets and 
then copy an existing column in the summary sheet (with the above formulas) 
and insert it in the appropriate location in the summary sheet so that it 
will refer to the proper column in the range of supporting sheets, the 
formulas refer to the new column but the results don't reflect the sum of the 
values in the new column.  The results reflect the sum of the values in the 
column from which the formulas were copied.  If I simply hit (F2) and enter, 
without changing the formulas, they will then produce the proper results.  
However, it seems to me that i shouldn't have to do this.  The formulas are 
correct without editing them.  Why don't the show the proper results 
automatically?  I've experimented with manual vs. automatic recalculation and 
with iterations to no avail.  Anybody have any ideas?  Is this a bug?
0
12/16/2008 5:18:11 PM
excel.newusers 15348 articles. 2 followers. Follow

4 Replies
908 Views

Similar Articles

[PageSpeed] 53

Tools>Options>Calculation tab, check Automatic

-- 
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Bill Schickling" <BillSchickling@discussions.microsoft.com> wrote in 
message news:8B8EF986-942A-4BED-A774-BE58F8B52216@microsoft.com...
> I'm using Excel 2003 w/ SP2 and have encountered a problem I don't recall
> having before.  I have a workbook where a summary sheet is used to 
> aggregate
> the values in each of the cells in the rest of the sheets by using a 
> formula
> with following syntax:
> =sum('sheet1:sheetx'!Cn),
> where "sheet1" is the name of the first sheet in the range of contiguous
> sheets, "sheetx" is the name of the last sheet in that range, "C" is the
> pertinent column letter for a cell (the columns in this workbook represent
> years) and "n" is the pertinent row number.
>
> When I insert a new column (for a new year) in the supporting worksheets 
> and
> then copy an existing column in the summary sheet (with the above 
> formulas)
> and insert it in the appropriate location in the summary sheet so that it
> will refer to the proper column in the range of supporting sheets, the
> formulas refer to the new column but the results don't reflect the sum of 
> the
> values in the new column.  The results reflect the sum of the values in 
> the
> column from which the formulas were copied.  If I simply hit (F2) and 
> enter,
> without changing the formulas, they will then produce the proper results.
> However, it seems to me that i shouldn't have to do this.  The formulas 
> are
> correct without editing them.  Why don't the show the proper results
> automatically?  I've experimented with manual vs. automatic recalculation 
> and
> with iterations to no avail.  Anybody have any ideas?  Is this a bug? 

0
nicolaus (2022)
12/16/2008 5:28:34 PM
Thanks for the response.  Have done that.  Have also set to manual and hit 
F9.  Neither resolves the problem.

"Niek Otten" wrote:

> Tools>Options>Calculation tab, check Automatic
> 
> -- 
> Kind regards,
> 
> Niek Otten
> Microsoft MVP - Excel
> 
> "Bill Schickling" <BillSchickling@discussions.microsoft.com> wrote in 
> message news:8B8EF986-942A-4BED-A774-BE58F8B52216@microsoft.com...
> > I'm using Excel 2003 w/ SP2 and have encountered a problem I don't recall
> > having before.  I have a workbook where a summary sheet is used to 
> > aggregate
> > the values in each of the cells in the rest of the sheets by using a 
> > formula
> > with following syntax:
> > =sum('sheet1:sheetx'!Cn),
> > where "sheet1" is the name of the first sheet in the range of contiguous
> > sheets, "sheetx" is the name of the last sheet in that range, "C" is the
> > pertinent column letter for a cell (the columns in this workbook represent
> > years) and "n" is the pertinent row number.
> >
> > When I insert a new column (for a new year) in the supporting worksheets 
> > and
> > then copy an existing column in the summary sheet (with the above 
> > formulas)
> > and insert it in the appropriate location in the summary sheet so that it
> > will refer to the proper column in the range of supporting sheets, the
> > formulas refer to the new column but the results don't reflect the sum of 
> > the
> > values in the new column.  The results reflect the sum of the values in 
> > the
> > column from which the formulas were copied.  If I simply hit (F2) and 
> > enter,
> > without changing the formulas, they will then produce the proper results.
> > However, it seems to me that i shouldn't have to do this.  The formulas 
> > are
> > correct without editing them.  Why don't the show the proper results
> > automatically?  I've experimented with manual vs. automatic recalculation 
> > and
> > with iterations to no avail.  Anybody have any ideas?  Is this a bug? 
> 
0
12/16/2008 5:35:00 PM
I'm thinking it must be a problem with this workbook.  I tried replicating 
the problem in a new workbook using a simple set of test data and was unable 
to do so.  What's really strange is that if I select one of the cells in the 
problem workbook that has failed to re-calculate, use Tools>Formula 
Auditing>Evaluate Formula and click on Evaluate, it will return the 
UN-recalculated value!  But if I then hit F2 and Enter on the same cell, it 
properly recalculates and displays the correct result.  Weird.

"Bill Schickling" wrote:

> Thanks for the response.  Have done that.  Have also set to manual and hit 
> F9.  Neither resolves the problem.
> 
> "Niek Otten" wrote:
> 
> > Tools>Options>Calculation tab, check Automatic
> > 
> > -- 
> > Kind regards,
> > 
> > Niek Otten
> > Microsoft MVP - Excel
> > 
> > "Bill Schickling" <BillSchickling@discussions.microsoft.com> wrote in 
> > message news:8B8EF986-942A-4BED-A774-BE58F8B52216@microsoft.com...
> > > I'm using Excel 2003 w/ SP2 and have encountered a problem I don't recall
> > > having before.  I have a workbook where a summary sheet is used to 
> > > aggregate
> > > the values in each of the cells in the rest of the sheets by using a 
> > > formula
> > > with following syntax:
> > > =sum('sheet1:sheetx'!Cn),
> > > where "sheet1" is the name of the first sheet in the range of contiguous
> > > sheets, "sheetx" is the name of the last sheet in that range, "C" is the
> > > pertinent column letter for a cell (the columns in this workbook represent
> > > years) and "n" is the pertinent row number.
> > >
> > > When I insert a new column (for a new year) in the supporting worksheets 
> > > and
> > > then copy an existing column in the summary sheet (with the above 
> > > formulas)
> > > and insert it in the appropriate location in the summary sheet so that it
> > > will refer to the proper column in the range of supporting sheets, the
> > > formulas refer to the new column but the results don't reflect the sum of 
> > > the
> > > values in the new column.  The results reflect the sum of the values in 
> > > the
> > > column from which the formulas were copied.  If I simply hit (F2) and 
> > > enter,
> > > without changing the formulas, they will then produce the proper results.
> > > However, it seems to me that i shouldn't have to do this.  The formulas 
> > > are
> > > correct without editing them.  Why don't the show the proper results
> > > automatically?  I've experimented with manual vs. automatic recalculation 
> > > and
> > > with iterations to no avail.  Anybody have any ideas?  Is this a bug? 
> > 
0
12/16/2008 8:56:12 PM
Very, very rarely Excel looses its way in the recalculation chain.
You can rebuild the recalc chain with CTRL+ALT+SHIFT+F9

-- 
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Bill Schickling" <BillSchickling@discussions.microsoft.com> wrote in 
message news:8EEC522E-65EE-4B0F-A083-301BDFF7E969@microsoft.com...
> I'm thinking it must be a problem with this workbook.  I tried replicating
> the problem in a new workbook using a simple set of test data and was 
> unable
> to do so.  What's really strange is that if I select one of the cells in 
> the
> problem workbook that has failed to re-calculate, use Tools>Formula
> Auditing>Evaluate Formula and click on Evaluate, it will return the
> UN-recalculated value!  But if I then hit F2 and Enter on the same cell, 
> it
> properly recalculates and displays the correct result.  Weird.
>
> "Bill Schickling" wrote:
>
>> Thanks for the response.  Have done that.  Have also set to manual and 
>> hit
>> F9.  Neither resolves the problem.
>>
>> "Niek Otten" wrote:
>>
>> > Tools>Options>Calculation tab, check Automatic
>> >
>> > -- 
>> > Kind regards,
>> >
>> > Niek Otten
>> > Microsoft MVP - Excel
>> >
>> > "Bill Schickling" <BillSchickling@discussions.microsoft.com> wrote in
>> > message news:8B8EF986-942A-4BED-A774-BE58F8B52216@microsoft.com...
>> > > I'm using Excel 2003 w/ SP2 and have encountered a problem I don't 
>> > > recall
>> > > having before.  I have a workbook where a summary sheet is used to
>> > > aggregate
>> > > the values in each of the cells in the rest of the sheets by using a
>> > > formula
>> > > with following syntax:
>> > > =sum('sheet1:sheetx'!Cn),
>> > > where "sheet1" is the name of the first sheet in the range of 
>> > > contiguous
>> > > sheets, "sheetx" is the name of the last sheet in that range, "C" is 
>> > > the
>> > > pertinent column letter for a cell (the columns in this workbook 
>> > > represent
>> > > years) and "n" is the pertinent row number.
>> > >
>> > > When I insert a new column (for a new year) in the supporting 
>> > > worksheets
>> > > and
>> > > then copy an existing column in the summary sheet (with the above
>> > > formulas)
>> > > and insert it in the appropriate location in the summary sheet so 
>> > > that it
>> > > will refer to the proper column in the range of supporting sheets, 
>> > > the
>> > > formulas refer to the new column but the results don't reflect the 
>> > > sum of
>> > > the
>> > > values in the new column.  The results reflect the sum of the values 
>> > > in
>> > > the
>> > > column from which the formulas were copied.  If I simply hit (F2) and
>> > > enter,
>> > > without changing the formulas, they will then produce the proper 
>> > > results.
>> > > However, it seems to me that i shouldn't have to do this.  The 
>> > > formulas
>> > > are
>> > > correct without editing them.  Why don't the show the proper results
>> > > automatically?  I've experimented with manual vs. automatic 
>> > > recalculation
>> > > and
>> > > with iterations to no avail.  Anybody have any ideas?  Is this a bug?
>> > 

0
nicolaus (2022)
12/16/2008 9:05:25 PM
Reply:

Similar Artilces:

SUM with WHERE
I have one sheet with a bunch of records that look like: Name, Lang, Qty Peter, English, 5 Peter, French, 2 Dave, English, 7 .... On a second sheet I want to create a summary of the records: English, 12 French, 2 So What I am kind of looking for is this: English, =sum of $DRECORDS where Lang=English French, =sum of $DRECORDS where Lang=French Any ideas how to do this in excel? Peter Look in the help index for SUMIF -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett1@austin.rr.com "Peter Carlson" <peter@h_o_w_u_d_o_d_a_t.com> wrote in message news:e1l3%233eXI...

How to apply headings to all worksheets
I have a customized heading and footnote which I want to use for all worksheets in one file. Can someone tell me how please? contor+tab of the worksheets . all worksheets will be selected now you type in sheet 1 whatever header you want. it will be repeated in all the sheets remember to select any one of the sheets so that selection of all sheets is deselected "bill" <bill@discussions.microsoft.com> wrote in message news:826B1F17-4BD9-48A5-9B54-9FF14A1D9023@microsoft.com... > I have a customized heading and footnote which I want to use for all > worksheets in one fil...

Sum duplicate lines in a table
I have a table which contains a list of products soted by poduct name. Against each product there is a stock quantity, each Item may appear more than once. I want to be create a query which will give me a list of the products, but with only one entry per item, with a total quantity for each item. Is this possible? Thanks Neil wrote: >I have a table which contains a list of products soted by poduct name. >Against each product there is a stock quantity, each Item may appear more >than once. > I want to be create a query which will give me a list of the products,...

Can the format of the destination cell be kept?
I keep track of the "state" of customers depending on different criteria and for that I have conditional format to change the color of the cells. Once a cycle ends, I need to keep record of the last state in another colum so that the new cycle can begin. The problem is that when I copy the old content into the new cell, it brings along its color and I need it to be plain white. I know that I could choose paste special and then just choose value and that would bring just the value without the color, but I want to copy the old contents in a shorter way. So is there a way...

Sum on horizontal lookup.
in one row (a10:aa10), i have a name in one cell, then value in the next, name in one cell, then value in the next... Some of the names in the row are repeated. Based on a lookup value (which will be one of the names), I want to: search the row for the lookup value add the value to the right each time the lookup value is found. example: a10 a11 a12 a13 a14 a15 a16 a17 errors 10 correct 10 errors 10 correct 20 lookup value "errors", result 20 lookup value "correct", result 30 =SUMIF(A10:Z10,"errors",B10:AA10) -- _____________...

Can I split a cell diagonally, with text in each triangle ?
I am putting together a calendar on excel and would like to split a cell diagnolly, colour each section and be able to type text in each triangle. Is this possible and if so how ? Many thanks Helen No it is not possible! (Now that I have said that someone will probably come in with a method) Youi could, however, make two triangles with different fills, and put text in them or make the fills partially tranparent and put text in the cells under them "Helen T" wrote: > I am putting together a calendar on excel and would like to split a cell > diagnolly, colour each ...

Running Sum 01-27-10
Hello, I have been looking at some running sum examples (http:// support.microsoft.com/kb/290136), but can not work out how to apply it to my query. I have Recieved, Recieved Amount .. then Cleared, Cleared Amount .. i am trying to work out a running backlog of work. So A: Recieved, Rec Amount .. Cleared, Clrd Amount .. Backlog, Backlog Amount. B: Recieved, Rec Amount .. Cleared, Clrd Amount .. Backlog of A - Cleared + Received. C: Recieved, Rec Amount .. Cleared, Clrd Amount .. Backlog of B - Cleared + Received. and so on? Im guessing its a DSUM thing, but i cant make th...

Formatting Cells Question
Hi I am having troubles with formatting cells in EXCEL 2003. I have a column of many hundreds of rows lthat contain data like this: 01Jan12 on fist glance this looks like a date but it really is not. EXCEL on the other automatically assumes it is a date and formats as such. 2001-Jan-12 I have tried formatting as text, General and even experimented with custom formats to no avail. In the same column I have 03Mar1543 Which is dispolayed exactly as is. How can I get EXCEL to simply display data like 01Jan12 exactly as is? You must format the cell as Text BEFORE typing any information...

Freeze cells
How do you freeze cells instead of the rows or columns. When i am working a fuction and try to copy from aw date formula it copys all of the cells I do not want it to copy the top cells. You use to vbe able to freeze the cells in Lotus can you freeze on cell in Excel???? Use absolute reference for the cells that you want to be fixed =A2+B2 instead use =$A$2+B2 when copied down it will increment B2 to B3 and so on but A2 will stay the same -- Regards, Peo Sjoblom "darlene lassiter" <darlene@twinstop.com> wrote in message news:003601c39266$a6c0f990$a301280a@phx.gbl...

How to Reference an Array to Count Values?
I need to reference a list of names in a column in one worksheet to count the number of times those names occur in a column in another worksheet in the same workbook. That is, check the name list and sum the number of times those names occur in another list. How do I do this? Take a look at the sumif function in help. -- HTH... Jim Thomlinson "Mashuganah" wrote: > I need to reference a list of names in a column in one worksheet to count the > number of times those names occur in a column in another worksheet in the > same workbook. > >...

Help on SUM cells with formulas
At F199 I have the formula =SUM(F177:F198) but rows 177 to 198 contain formulas like =(+E180-E179) and F199 formula won't work. Do I need to extract correctly the values only? is it possible? Thanks much. Benjamin What exactly does "formula won't work" mean? What kind of return are you getting? No answer? - Wrong answer? - Error message? -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! -------------------------------------------------------------------...

Need to seperate alpha numeric data in a cell
I have a number of cells that contain words and numbers. How can I ge rid of or seperate the numbers form the data. e.g Cell A1 contains - Bridge Terminal 123456 I need to just get 123456 Please note numbers can be anywhere in the data and are differen lengths. Thanks in advance Andre -- Message posted from http://www.ExcelForum.com I think you need a user defined function (UDF) for this. Go to the VB editor, insert a new module and enter the following: Function OnlyNumber(xstrIn) Dim i As Integer Dim strReturn As String For i = 1 To Len(xstrIn) If IsNumeric(Mid(xstrIn, i, 1)) Then st...

How to Sum a column if reference column is blank
How do I sum a cloumn of numbers when my reference column is blank? >when my reference column is blank? What does that mean? -- Mike When competing hypotheses are equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. Occam''''s razor (Abbrev) "Rusty" wrote: > How do I sum a cloumn of numbers when my reference column is blank? See if this is what you had in mind... 96...x 51..... 22...p 73..... 81..... Sum A1:A5 where the corresponding cell in B1:B5 is blank/empty: ...

Excel Crashing using Remote Worksheet Calls in NETWORKDAYS
Hi all... We're having problems attempting with Excel 2007 crashing when referencing a lookup table in Worksheet 'B' from Worksheet 'A' during a NETWORKDAYS call.... In more detail... Worksheet 'A' has a list of rows including two date columns. We're attempting to workout the difference in working days between the two date columns taking into account a list of "holiday" dates contained within Worksheet 'B'. We are using NETWORKDAYS function for this. Everything works fine whilst Worksheet 'B' is open, however when you close Works...

drop-down menus and nested references
I am trying to make a series of drop-down menus wherein the list used for the second drop-down changes based on what is selected in the first drop-down. Because there are more than seven options in the first menu, I can't just do an =IF() function. Is there any way to force the calculation of a reference within another formula? For example, if I have named lists A, B, and C and the first drop-down menu in A1 contains the options A, B, and C, how do I get the drop-down menu in A2 to read A1 as a list name instead of a text item? If there is no way to do this, does anyone have alte...

Sum, Sum if or if???????
Two things: I inherited a spreadsheet and everytime I save or update a cell I get message that reads "The macros in this project have been disabled Please refer to online help etc to enable". How can I get rid of this message, as as far as I know I not set up an macros and there are none in the list after checking the macro list (the spreadsheet I have enclosed for question 2 example does this i you enter or change an amount)! 2. Can you help with a formula, basically I want excel to provide dat if any particular figure is over a certain amount. see attache example. Thanks in ad...

cell watch function in excel 2001 for mac? #2
Thanks JE McGimpsey -- mike1 ----------------------------------------------------------------------- mike10's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1441 View this thread: http://www.excelforum.com/showthread.php?threadid=26193 ...

count or sum
how can I count all the occupied cells in the same column if the cells contain text and numbers? A row 1 br1 row 2 row 3 row 4 cb2 row 5 row 6 row 7 df6 ect down to row 200. the answer should be 3 (the number of cells occupied) please help thanks rgs jerie use: =COUNTA(A:A) Regards Trevor "jerie" <anonymous@discussions.microsoft.com> wrote in message news:4a1c01c5214e$19ffd710$a401280a@phx.gbl... > how can I count all the occupied cells in the same column > if the cells contain text and numbers? > ...

Linked cells and text boxes
My query is this: I have created a questionnaire answer template comprising of two worksheets. One contains the answers and the second is a score sheet that links to the cells in the answer sheet. When linking cells between the workbooks, the numerical ones are fine. However, a lot of the answers are in text form so to get round that, I set up text boxes in the score sheet to link to the cells in the answer sheet. That almost works apart from one thing: As I type the text in the cell in the answer sheet, the text box in the score sheet does not carry all of the text. Some stop in mid...

Synchronize data across 2 sheets
Hi all Excel 2007 Workbook with a sheet called 2010 around 700 rows/records and a sheet called Previous around 5000 rows records. Both have 31 columns and identical column headers On the 2010 sheet there are records that have changed, how do I get the changed records details onto and overwite the same record on the Previous sheet. Not sure if it would help but each record has a unique reference number. How do i do this ? Hope this makes sense thanks for any help offered Hi If you know which colums are to be changed I would use vlookup function. Vlookup(lookup valu...

Can I get a cell to automatically rezero after entering a value. H
I'm trying to create a spreadsheet that calculates inventory with a column for initial inventory), total inventory used, Inventory remaining, and a column where you add in the quantity used for the last order and it automatically deducts the quantity used from the balance. I'm running into a problem where the number that I enter in the 'quantity used' column remains and when I make other changes in the same row, it deducts the quantity used an additional time. If I could get the value that I enter to go to zero, or the cell to clear after I enter a value, my prob...

Recalc not working
In one of my forms I want a subform to be updated the moment I enter it and one of it's combobox be reflecting all the entries in one of the filed in the parent/major form. However, Private Sub CrTrans_Enter() Me.Recalc End Sub doesn't seem to be carryingout the same. Any idea? -- Thanx in advance, Best Regards, Faraz On Thu, 11 Feb 2010 02:45:01 -0800, Faraz A. Qureshi <FarazAQureshi@discussions.microsoft.com> wrote: How about Me.Requery? -Tom. Microsoft Access MVP >In one of my forms I want a subform to be updated the moment I enter it...

Number above next Cell
Have a Sheet with codes : A B C D C 1 T G G G 12 2 G G G T 11 3 G G T T 23 (each Cell is 16x16 pixels) The G's and T's are codes (Format as "T 12" (Letter-Space-Space-Number)) The Number part should be hidden under the next Cell but in each line the last Cell it is above this Cell. Want it to be like this : A B C D C 1 T G G G 2 G G G T 3 G G T T Anybody ? Put a space character in the cell to the right. In article <2baaf$42d3d223$546b00f2$28902@news.multikabel.nl>, "Roby" <r.udo@quicknet.nl> wrote: > Have a Sheet with code...

Trying to create conditional format cell
trying to automate a form. Have 1st part of what I want to do but don't know how to do the rest. Example Cell "C" = Cell A minus cell B . Now what I want to do is this: if Cell C is 10 percent less than Cell D then text is Red, Bold, Fill is yellow. is this Possible? Not very good at math functions. select cell C2. Format - Conditional Format. Change first box to "Formula is". In second box, input this: =C2<0.9*D2 Click the format button, on text tab, select bold, and on pattern tab, choose yellow. Ok out. (assuming you meant 10% less or more) -- ...

Dates in cell
I want to be able to add a date to cells without having to use the /; however when I enter the date as 111803 the results of the cell is 2/7/2206. What am I not doing??? Thanks in advance Sondra Your not entering the / as you already know. Excel will not recognise it a s a date without the / For possible solutions and a better understanding try http://www.cpearson.com/excel/datetime.htm http://www.cpearson.com/excel/DateTimeEntry.htm -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England nick_hodgeTAKETHISOUT@zen.co.uk.ANDTHIS Sondra wrote: > I want to be able to add a...