Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Recalc of SUM() that refers to a cell across a range of worksheets
ToolsOptionsCalculation tab, check Automatic
-- Kind regards, Niek Otten Microsoft MVP - Excel "Bill Schickling" wrote in message ... 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? |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Recalc of SUM() that refers to a cell across a range of worksh
Thanks for the response. Have done that. Have also set to manual and hit
F9. Neither resolves the problem. "Niek Otten" wrote: ToolsOptionsCalculation tab, check Automatic -- Kind regards, Niek Otten Microsoft MVP - Excel "Bill Schickling" wrote in message ... 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? |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Recalc of SUM() that refers to a cell across a range of worksh
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 ToolsFormula AuditingEvaluate 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: ToolsOptionsCalculation tab, check Automatic -- Kind regards, Niek Otten Microsoft MVP - Excel "Bill Schickling" wrote in message ... 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? |
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Recalc of SUM() that refers to a cell across a range of worksh
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" wrote in message ... 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 ToolsFormula AuditingEvaluate 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: ToolsOptionsCalculation tab, check Automatic -- Kind regards, Niek Otten Microsoft MVP - Excel "Bill Schickling" wrote in message ... 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Range Name Limitations - Max Refers To Length? | Excel Discussion (Misc queries) | |||
recalc setting by range | Excel Discussion (Misc queries) | |||
countif argument for 3 occurences of which 1 refers to a range | Excel Discussion (Misc queries) | |||
toggling which worksheet a named range refers to | Excel Discussion (Misc queries) | |||
named range refers to: in a chart | Excel Discussion (Misc queries) |