Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Updating linked cells within a workbook, from worksheet to workshe
(Excel 2002) I have a 5G Excel workbook with around 40 worksheets in it.
Many of the sheets contain cells that refer to values in other sheets. I recently found that some of the values were incorrect (I believe they were just not updated). When I double clicked on the cell, gained access to the formula and then hit enter, the cell updated. I'm wondering 2 things - 1) Under what circumstances will cells NOT automatically update? I thought they always updated automatically as long as you're working within the same workbook. 2) Is there a way that I can update all cells at once? |
#2
|
|||
|
|||
is calculation set to manual or automatic (tools / option / calculation tab)
pressing the F9 key should update all formulas Cheers JulieD "notloiseweiss" wrote in message ... (Excel 2002) I have a 5G Excel workbook with around 40 worksheets in it. Many of the sheets contain cells that refer to values in other sheets. I recently found that some of the values were incorrect (I believe they were just not updated). When I double clicked on the cell, gained access to the formula and then hit enter, the cell updated. I'm wondering 2 things - 1) Under what circumstances will cells NOT automatically update? I thought they always updated automatically as long as you're working within the same workbook. 2) Is there a way that I can update all cells at once? |
#3
|
|||
|
|||
Hi,
Check the options: Tools - Options - Calculation. You can apply settings that automatically calculates each time it registers a change to a value that is used in calculations. Alternatively you can change it so that you have to manually tell it to calculate each time. F9 re-calculates the workbook. When you have such great capacity Excel may struggle to calculate - check your status bar and it will report back on how far through the calculation process it is as a percentage. Cheers, Jon "notloiseweiss" wrote: (Excel 2002) I have a 5G Excel workbook with around 40 worksheets in it. Many of the sheets contain cells that refer to values in other sheets. I recently found that some of the values were incorrect (I believe they were just not updated). When I double clicked on the cell, gained access to the formula and then hit enter, the cell updated. I'm wondering 2 things - 1) Under what circumstances will cells NOT automatically update? I thought they always updated automatically as long as you're working within the same workbook. 2) Is there a way that I can update all cells at once? |
#4
|
|||
|
|||
Thanks for the response.
I didn't know about this Tools - Options - Calculation setting, so it is useful to know. Unfortunately, my setting is already on update. I was aware of the 'status bar', so I don't think that was it either... "Springbok" wrote: Hi, Check the options: Tools - Options - Calculation. You can apply settings that automatically calculates each time it registers a change to a value that is used in calculations. Alternatively you can change it so that you have to manually tell it to calculate each time. F9 re-calculates the workbook. When you have such great capacity Excel may struggle to calculate - check your status bar and it will report back on how far through the calculation process it is as a percentage. Cheers, Jon "notloiseweiss" wrote: (Excel 2002) I have a 5G Excel workbook with around 40 worksheets in it. Many of the sheets contain cells that refer to values in other sheets. I recently found that some of the values were incorrect (I believe they were just not updated). When I double clicked on the cell, gained access to the formula and then hit enter, the cell updated. I'm wondering 2 things - 1) Under what circumstances will cells NOT automatically update? I thought they always updated automatically as long as you're working within the same workbook. 2) Is there a way that I can update all cells at once? |
#5
|
|||
|
|||
Thanks for the response.
I didn't know about this Tools - Options - Calculation setting, so it is useful to know. Unfortunately, my setting is already on update. The F9 is also good to know about, although it sounds like I shouldn't need to use it since I already have automatic updates on... "JulieD" wrote: is calculation set to manual or automatic (tools / option / calculation tab) pressing the F9 key should update all formulas Cheers JulieD "notloiseweiss" wrote in message ... (Excel 2002) I have a 5G Excel workbook with around 40 worksheets in it. Many of the sheets contain cells that refer to values in other sheets. I recently found that some of the values were incorrect (I believe they were just not updated). When I double clicked on the cell, gained access to the formula and then hit enter, the cell updated. I'm wondering 2 things - 1) Under what circumstances will cells NOT automatically update? I thought they always updated automatically as long as you're working within the same workbook. 2) Is there a way that I can update all cells at once? |
#6
|
|||
|
|||
I've never seen this happen in real life, but there have been a few posts that
describe your problem. One suggestion is to try to wake up excel's calculation engine (may be the wrong term!): Edit|replace what: = (equal sign) with: = (equal sign) replace all And under the Options button, you can choose Workbook so that you don't have to do each worksheet individually. There have been replies that say this works, but also questioning why this happens and will it fix it forever. I don't have a guess for either question. notloiseweiss wrote: (Excel 2002) I have a 5G Excel workbook with around 40 worksheets in it. Many of the sheets contain cells that refer to values in other sheets. I recently found that some of the values were incorrect (I believe they were just not updated). When I double clicked on the cell, gained access to the formula and then hit enter, the cell updated. I'm wondering 2 things - 1) Under what circumstances will cells NOT automatically update? I thought they always updated automatically as long as you're working within the same workbook. 2) Is there a way that I can update all cells at once? -- Dave Peterson |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Updating linked cells within a workbook, from worksheet to workshe
I run into the exact situation as #1 below. The settings for calculation are
set to auto, but i still have to double-click in one of the cells in order for all to recalculate. In an unrelated Excel problem, I have successfully built links in several spreadsheets/workbooks, however, on occassion, cells do not update/refresh upon opening and "updating". Sometimes I have to open the supporting sheets or books, save them and then re-open my target sheet and select update, then the values are updated. Why is this? George P. "notloiseweiss" wrote: (Excel 2002) I have a 5G Excel workbook with around 40 worksheets in it. Many of the sheets contain cells that refer to values in other sheets. I recently found that some of the values were incorrect (I believe they were just not updated). When I double clicked on the cell, gained access to the formula and then hit enter, the cell updated. I'm wondering 2 things - 1) Under what circumstances will cells NOT automatically update? I thought they always updated automatically as long as you're working within the same workbook. 2) Is there a way that I can update all cells at once? |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Updating linked cells within a workbook, from worksheet to wor
I have a similar problem in Excel 2003. A cell linked to another cell in a
different worksheet in the same workbook does not update unless one or other of the two cells is manually edited. Yet a later version of the same workbook updates correctly. There are no links to external files and calculation is set to manual. I suspect that somehow Excel has lost track of whether the cell has been updated or not (it only recalulates changed cells or cells based on changed cells) as at one stage I had the "Iterations" option unticked. In the later version of the file I ticked that option. However I cannot recreate the error which is frustrating, so I cannot confirm this hypothesis. Interesting that George P and I have both had the same or similar problems within a few weeks, 2 years after the initial posting. "George P" wrote: I run into the exact situation as #1 below. The settings for calculation are set to auto, but i still have to double-click in one of the cells in order for all to recalculate. In an unrelated Excel problem, I have successfully built links in several spreadsheets/workbooks, however, on occassion, cells do not update/refresh upon opening and "updating". Sometimes I have to open the supporting sheets or books, save them and then re-open my target sheet and select update, then the values are updated. Why is this? George P. "notloiseweiss" wrote: (Excel 2002) I have a 5G Excel workbook with around 40 worksheets in it. Many of the sheets contain cells that refer to values in other sheets. I recently found that some of the values were incorrect (I believe they were just not updated). When I double clicked on the cell, gained access to the formula and then hit enter, the cell updated. I'm wondering 2 things - 1) Under what circumstances will cells NOT automatically update? I thought they always updated automatically as long as you're working within the same workbook. 2) Is there a way that I can update all cells at once? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Linked cells show wrong number | Links and Linking in Excel | |||
Locked cells not updating | Excel Worksheet Functions | |||
Update linked cells within a workbook??? | Links and Linking in Excel | |||
updating linked cells | Excel Discussion (Misc queries) | |||
Sorting mixed up linked cells in a workbook? | Excel Worksheet Functions |