Home |
Search |
Today's Posts |
#1
|
|||
|
|||
refreshing formulas in cells
Hello
I have a simple sum formula in Cell A2 that adds two cells "=sum(A1:b1)" for example. For some reason when I change the numbers or add numbers to Cell A1 or B1 the result of the formula dosent change in Cell A2 unless I page down and page up and then It shows the new total. Ive used formulas for years and every time I made a change to a number you could see the result in the cell containing the Sum formula right away even though I was in the same frame of that worksheet. My Auto calculate is checked vs using manual calculation under ToolsOptions. Im using Office 2000. What causes this? -- He4Giv (Dick) |
#2
|
|||
|
|||
I don't really have a guess, but if you start a new workbook and try this, does
it suffer from the same problem? If not, how about trying this against the troublesome workbook. Ctrl-a (twice in xl2003) to select all the cells edit|replace what: = (equal sign) with: = (equal sign) replace all Maybe this will wake up excel's calculation engine by forcing it to reevaluate all your formulas. (maybe not, though...) He4Giv wrote: Hello I have a simple sum formula in Cell A2 that adds two cells "=sum(A1:b1)" for example. For some reason when I change the numbers or add numbers to Cell A1 or B1 the result of the formula dosent change in Cell A2 unless I page down and page up and then It shows the new total. Ive used formulas for years and every time I made a change to a number you could see the result in the cell containing the Sum formula right away even though I was in the same frame of that worksheet. My Auto calculate is checked vs using manual calculation under ToolsOptions. Im using Office 2000. What causes this? -- He4Giv (Dick) -- Dave Peterson |
#3
|
|||
|
|||
Ps. I've never seen anything like this, but there have a few posts that said
this sometimes helps. Dave Peterson wrote: I don't really have a guess, but if you start a new workbook and try this, does it suffer from the same problem? If not, how about trying this against the troublesome workbook. Ctrl-a (twice in xl2003) to select all the cells edit|replace what: = (equal sign) with: = (equal sign) replace all Maybe this will wake up excel's calculation engine by forcing it to reevaluate all your formulas. (maybe not, though...) He4Giv wrote: Hello I have a simple sum formula in Cell A2 that adds two cells "=sum(A1:b1)" for example. For some reason when I change the numbers or add numbers to Cell A1 or B1 the result of the formula dosent change in Cell A2 unless I page down and page up and then It shows the new total. Ive used formulas for years and every time I made a change to a number you could see the result in the cell containing the Sum formula right away even though I was in the same frame of that worksheet. My Auto calculate is checked vs using manual calculation under ToolsOptions. Im using Office 2000. What causes this? -- He4Giv (Dick) -- Dave Peterson -- Dave Peterson |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
refreshing formulas in cells
I have also had this problem and never saw anything like it before. In my
situation, creating a new file did not prevent the problem. Also, it seems to respond to Save As or deleting blank columns. Then it magically shows the new values. The strange thing is it starts refreshing all cells and then stops. The base calcs change and some of the vlookups referencing them change. It then freezes. In some cases, you'll see the same number repeated on each row (the one it becomes stuck on gets repeated). Once unstuck the file works fine until you add new content, then it freezes again and I repeat the steps above. I assume this is a problem in 2003 and am concerned that any complicated analysis is unstable due to this problem. Has this been identified? Are they working on it? "Dave Peterson" wrote: Ps. I've never seen anything like this, but there have a few posts that said this sometimes helps. Dave Peterson wrote: I don't really have a guess, but if you start a new workbook and try this, does it suffer from the same problem? If not, how about trying this against the troublesome workbook. Ctrl-a (twice in xl2003) to select all the cells edit|replace what: = (equal sign) with: = (equal sign) replace all Maybe this will wake up excel's calculation engine by forcing it to reevaluate all your formulas. (maybe not, though...) He4Giv wrote: Hello I have a simple sum formula in Cell A2 that adds two cells "=sum(A1:b1)" for example. For some reason when I change the numbers or add numbers to Cell A1 or B1 the result of the formula dosent change in Cell A2 unless I page down and page up and then It shows the new total. Ive used formulas for years and every time I made a change to a number you could see the result in the cell containing the Sum formula right away even though I was in the same frame of that worksheet. My Auto calculate is checked vs using manual calculation under ToolsOptions. Im using Office 2000. What causes this? -- He4Giv (Dick) -- Dave Peterson -- Dave Peterson |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
refreshing formulas in cells
Try rebuilding the dependency tree with CTRL+ALT+SHIFT+F9
-- Kind regards, Niek Otten "Daigle" wrote in message ... I have also had this problem and never saw anything like it before. In my situation, creating a new file did not prevent the problem. Also, it seems to respond to Save As or deleting blank columns. Then it magically shows the new values. The strange thing is it starts refreshing all cells and then stops. The base calcs change and some of the vlookups referencing them change. It then freezes. In some cases, you'll see the same number repeated on each row (the one it becomes stuck on gets repeated). Once unstuck the file works fine until you add new content, then it freezes again and I repeat the steps above. I assume this is a problem in 2003 and am concerned that any complicated analysis is unstable due to this problem. Has this been identified? Are they working on it? "Dave Peterson" wrote: Ps. I've never seen anything like this, but there have a few posts that said this sometimes helps. Dave Peterson wrote: I don't really have a guess, but if you start a new workbook and try this, does it suffer from the same problem? If not, how about trying this against the troublesome workbook. Ctrl-a (twice in xl2003) to select all the cells edit|replace what: = (equal sign) with: = (equal sign) replace all Maybe this will wake up excel's calculation engine by forcing it to reevaluate all your formulas. (maybe not, though...) He4Giv wrote: Hello I have a simple sum formula in Cell A2 that adds two cells "=sum(A1:b1)" for example. For some reason when I change the numbers or add numbers to Cell A1 or B1 the result of the formula dosent change in Cell A2 unless I page down and page up and then It shows the new total. Ive used formulas for years and every time I made a change to a number you could see the result in the cell containing the Sum formula right away even though I was in the same frame of that worksheet. My Auto calculate is checked vs using manual calculation under ToolsOptions. Im using Office 2000. What causes this? -- He4Giv (Dick) -- Dave Peterson -- Dave Peterson |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
refreshing formulas in cells
What do you mean by rebuilding the dependency tree? Is this a way to
jumpstart the f9 key? "Niek Otten" wrote: Try rebuilding the dependency tree with CTRL+ALT+SHIFT+F9 -- Kind regards, Niek Otten "Daigle" wrote in message ... I have also had this problem and never saw anything like it before. In my situation, creating a new file did not prevent the problem. Also, it seems to respond to Save As or deleting blank columns. Then it magically shows the new values. The strange thing is it starts refreshing all cells and then stops. The base calcs change and some of the vlookups referencing them change. It then freezes. In some cases, you'll see the same number repeated on each row (the one it becomes stuck on gets repeated). Once unstuck the file works fine until you add new content, then it freezes again and I repeat the steps above. I assume this is a problem in 2003 and am concerned that any complicated analysis is unstable due to this problem. Has this been identified? Are they working on it? "Dave Peterson" wrote: Ps. I've never seen anything like this, but there have a few posts that said this sometimes helps. Dave Peterson wrote: I don't really have a guess, but if you start a new workbook and try this, does it suffer from the same problem? If not, how about trying this against the troublesome workbook. Ctrl-a (twice in xl2003) to select all the cells edit|replace what: = (equal sign) with: = (equal sign) replace all Maybe this will wake up excel's calculation engine by forcing it to reevaluate all your formulas. (maybe not, though...) He4Giv wrote: Hello I have a simple sum formula in Cell A2 that adds two cells "=sum(A1:b1)" for example. For some reason when I change the numbers or add numbers to Cell A1 or B1 the result of the formula dosent change in Cell A2 unless I page down and page up and then It shows the new total. Ive used formulas for years and every time I made a change to a number you could see the result in the cell containing the Sum formula right away even though I was in the same frame of that worksheet. My Auto calculate is checked vs using manual calculation under ToolsOptions. Im using Office 2000. What causes this? -- He4Giv (Dick) -- Dave Peterson -- Dave Peterson |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
refreshing formulas in cells
I have seen a similar problem (is it the same one) If you have userdefined functions referenced from the worksheet. If the userdefined functions do not have error trapping in them (on error goto etc) then if such an untrapped error arises it stops all calculation from that point on without warning. They can be really difficult to track down. So put error trapping in all functions. -- tony h ------------------------------------------------------------------------ tony h's Profile: http://www.excelforum.com/member.php...o&userid=21074 View this thread: http://www.excelforum.com/showthread...hreadid=329016 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
refreshing formulas in cells
Very, very rarely, Excel loses track of which cells depend on which others
(the "dependency tree"). This key combination (available in later versions of Excel, I don't know exactly from which one) analyses the workbook from scratch and rebuilds the dependency tree. Several successful tries have been reported in these newsgroups. -- Kind regards, Niek Otten "Daigle" wrote in message ... What do you mean by rebuilding the dependency tree? Is this a way to jumpstart the f9 key? "Niek Otten" wrote: Try rebuilding the dependency tree with CTRL+ALT+SHIFT+F9 -- Kind regards, Niek Otten "Daigle" wrote in message ... I have also had this problem and never saw anything like it before. In my situation, creating a new file did not prevent the problem. Also, it seems to respond to Save As or deleting blank columns. Then it magically shows the new values. The strange thing is it starts refreshing all cells and then stops. The base calcs change and some of the vlookups referencing them change. It then freezes. In some cases, you'll see the same number repeated on each row (the one it becomes stuck on gets repeated). Once unstuck the file works fine until you add new content, then it freezes again and I repeat the steps above. I assume this is a problem in 2003 and am concerned that any complicated analysis is unstable due to this problem. Has this been identified? Are they working on it? "Dave Peterson" wrote: Ps. I've never seen anything like this, but there have a few posts that said this sometimes helps. Dave Peterson wrote: I don't really have a guess, but if you start a new workbook and try this, does it suffer from the same problem? If not, how about trying this against the troublesome workbook. Ctrl-a (twice in xl2003) to select all the cells edit|replace what: = (equal sign) with: = (equal sign) replace all Maybe this will wake up excel's calculation engine by forcing it to reevaluate all your formulas. (maybe not, though...) He4Giv wrote: Hello I have a simple sum formula in Cell A2 that adds two cells "=sum(A1:b1)" for example. For some reason when I change the numbers or add numbers to Cell A1 or B1 the result of the formula dosent change in Cell A2 unless I page down and page up and then It shows the new total. Ive used formulas for years and every time I made a change to a number you could see the result in the cell containing the Sum formula right away even though I was in the same frame of that worksheet. My Auto calculate is checked vs using manual calculation under ToolsOptions. Im using Office 2000. What causes this? -- He4Giv (Dick) -- Dave Peterson -- Dave Peterson |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
refreshing formulas in cells
Depending on the version of Excel, even with error trapping you can
experience this problem in at least one version of Excel. I have a few workbooks which I never managed to run correctly in Excel97. -- Kind regards, Niek Otten "tony h" wrote in message ... I have seen a similar problem (is it the same one) If you have userdefined functions referenced from the worksheet. If the userdefined functions do not have error trapping in them (on error goto etc) then if such an untrapped error arises it stops all calculation from that point on without warning. They can be really difficult to track down. So put error trapping in all functions. -- tony h ------------------------------------------------------------------------ tony h's Profile: http://www.excelforum.com/member.php...o&userid=21074 View this thread: http://www.excelforum.com/showthread...hreadid=329016 |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
refreshing formulas in cells
No user defined functions, just lots of repetitions of the basic ones. I
pulled up a version of the file that I saved stuck repeating the wrong vlookup value in several rows. The Ctrl+Alt+Shft+F9 did cause the file to refresh (correctly). This seems to work. Will I need to do this everytime I make major changes? I am concerned that it may not always be so obvious that Excel is not refreshing. Thanks! "tony h" wrote: I have seen a similar problem (is it the same one) If you have userdefined functions referenced from the worksheet. If the userdefined functions do not have error trapping in them (on error goto etc) then if such an untrapped error arises it stops all calculation from that point on without warning. They can be really difficult to track down. So put error trapping in all functions. -- tony h ------------------------------------------------------------------------ tony h's Profile: http://www.excelforum.com/member.php...o&userid=21074 View this thread: http://www.excelforum.com/showthread...hreadid=329016 |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
refreshing formulas in cells
<The Ctrl+Alt+Shft+F9 did cause the file to refresh (correctly).
Fine!! -- Kind regards, Niek Otten "Daigle" wrote in message ... No user defined functions, just lots of repetitions of the basic ones. I pulled up a version of the file that I saved stuck repeating the wrong vlookup value in several rows. The Ctrl+Alt+Shft+F9 did cause the file to refresh (correctly). This seems to work. Will I need to do this everytime I make major changes? I am concerned that it may not always be so obvious that Excel is not refreshing. Thanks! "tony h" wrote: I have seen a similar problem (is it the same one) If you have userdefined functions referenced from the worksheet. If the userdefined functions do not have error trapping in them (on error goto etc) then if such an untrapped error arises it stops all calculation from that point on without warning. They can be really difficult to track down. So put error trapping in all functions. -- tony h ------------------------------------------------------------------------ tony h's Profile: http://www.excelforum.com/member.php...o&userid=21074 View this thread: http://www.excelforum.com/showthread...hreadid=329016 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
refreshing formulas in cells | Excel Discussion (Misc queries) | |||
In Exel 2000, stop the blank cells (with formulas) from printing. | Excel Discussion (Misc queries) | |||
delete values in several cells without deleting the formulas | Excel Discussion (Misc queries) | |||
How do I view formulas by values not by which cells they use to c. | Excel Discussion (Misc queries) | |||
Protected cells -automatically format to a different color | Excel Discussion (Misc queries) |