Home |
Search |
Today's Posts |
|
#1
|
|||
|
|||
Formula do not work until edited
Many of my users have ocassional instances where a formula is copied
from adjacent cells and will not work until the cursor is placed in the edit bar and the formula edit is confirmed (with no changes). Once the 'no change' edit is done, the cell value will change from VALUE to the correct result. I have seen similiar reports of this in many groups, but no one seems to be able to say why this is happening, they only talk about work arounds after the problem has happened, but I would really like to know if there is a way of preventing it. We use Excel 2003. |
#2
|
|||
|
|||
My first guess is that the user has calculation set to manual. But I don't
think I've seen the cell change from a good value to an error then back to the correct value. But I'd check there. If that doesn't help, maybe you could post the formula causing the trouble???? KiwiSteve wrote: Many of my users have ocassional instances where a formula is copied from adjacent cells and will not work until the cursor is placed in the edit bar and the formula edit is confirmed (with no changes). Once the 'no change' edit is done, the cell value will change from VALUE to the correct result. I have seen similiar reports of this in many groups, but no one seems to be able to say why this is happening, they only talk about work arounds after the problem has happened, but I would really like to know if there is a way of preventing it. We use Excel 2003. -- Dave Peterson |
#3
|
|||
|
|||
The primary cause is that calculation is turned off
Tools, Options, Calculation (tab), make calculation automatic. There are other possibilities, such as the format was changed from text to something else, but is not effective until reentered. If you want to reenter a lot of formulas, select all cells on the sheet Ctrl+A (or if Excel 2003 Ctrl+Shift+SpaceBar) then Bring up the Replace dialog Ctrl+H change from: = (equal sign) change to: = (equal sign) which will work even with Calculation off, but I would suggest that you turn calculation on, just in case the order makes a difference. The first workbook opened determines whether calcualtion is turned on or off. Usually this will be your personal.xls workbook. --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "KiwiSteve" wrote in message ups.com... Many of my users have ocassional instances where a formula is copied from adjacent cells and will not work until the cursor is placed in the edit bar and the formula edit is confirmed (with no changes). Once the 'no change' edit is done, the cell value will change from VALUE to the correct result. I have seen similiar reports of this in many groups, but no one seems to be able to say why this is happening, they only talk about work arounds after the problem has happened, but I would really like to know if there is a way of preventing it. We use Excel 2003. |
#4
|
|||
|
|||
Thanks to both Dave and David for the two replies. Calculation is set
to automatic and I tried F9 a few times just to be safe. You can never be sure when helping someone else after the fact, but the user says she created two new adjacent columns using the insert command on the column header and then dragged the existing formula and cell values from the adjacent left column. At that point the new column had value errors. After tracing the origin of the value error to one particular cell in each column , I tried Format Cell Number format on all feeder cells with no change. I looked at all of the Tools-Options tabs and their settings and saw nothing that I recognised as affecting the results. At the suggestion of a colleague I even copied the working and non working cell formula to the clipboard and pasted them into Notepad. Apart from expected column reference differences there were no differences to the formula. I then eventually found that doing any of the following to the non working cell fixed the problem. 1) Editing the cell in the edit bar (with no change) 2) Using David's replace equals with equals edit 3) Dragging the left hand working cell over the non working cell (which is what the user says she did in the first place to fill the cell. Using the format painter did not fix the problem. It really acts as if Excel has internally lost track of what these non working cells are all about and then resets itself once the cell is edited. Now that we know Davids work around, its no big deal, but I would have liked to know the conditions that trigger this in an attempt to avoid it in the future. As requested the formula is =(CEILING(SUM(H35:H43),1)) where the range has cells that have calculated results that are all working ok. As an aside I have seen many times a similiar problem with data we import into XL from external sources. Even though a cell contains a numeric value and is formatted as numeric, XL will complain that the cell has an error and wont calculate mathmatical results correctly until we do the 'no change' edit. The only menu command that allows us to fix the problem in mass is the Data-Text to columns command. The other clumsy solution I have is a macro that loops through every populated cell and does a non destructive edit on the value. Again XL appears to be acting as if it has lost track of what is going on until after the edit. The text to columns solution is a pain as I only know how to apply it one column at a time and my macro is real slow (but faster than doing it by hand). Once again, if I understood the failure mode, maybe I could avoid it. |
#5
|
|||
|
|||
"KiwiSteve" wrote in message
oups.com Thanks to both Dave and David for the two replies. Calculation is set to automatic and I tried F9 a few times just to be safe. You can never be sure when helping someone else after the fact, but the user says she created two new adjacent columns using the insert command on the column header and then dragged the existing formula and cell values from the adjacent left column. At that point the new column had value errors. After tracing the origin of the value error to one particular cell in each column , I tried Format Cell Number format on all feeder cells with no change. I looked at all of the Tools-Options tabs and their settings and saw nothing that I recognised as affecting the results. At the suggestion of a colleague I even copied the working and non working cell formula to the clipboard and pasted them into Notepad. Apart from expected column reference differences there were no differences to the formula. I then eventually found that doing any of the following to the non working cell fixed the problem. 1) Editing the cell in the edit bar (with no change) 2) Using David's replace equals with equals edit 3) Dragging the left hand working cell over the non working cell (which is what the user says she did in the first place to fill the cell. Using the format painter did not fix the problem. It really acts as if Excel has internally lost track of what these non working cells are all about and then resets itself once the cell is edited. Now that we know Davids work around, its no big deal, but I would have liked to know the conditions that trigger this in an attempt to avoid it in the future. As requested the formula is =(CEILING(SUM(H35:H43),1)) where the range has cells that have calculated results that are all working ok. As an aside I have seen many times a similiar problem with data we import into XL from external sources. Even though a cell contains a numeric value and is formatted as numeric, XL will complain that the cell has an error and wont calculate mathmatical results correctly until we do the 'no change' edit. The only menu command that allows us to fix the problem in mass is the Data-Text to columns command. The other clumsy solution I have is a macro that loops through every populated cell and does a non destructive edit on the value. Again XL appears to be acting as if it has lost track of what is going on until after the edit. The text to columns solution is a pain as I only know how to apply it one column at a time and my macro is real slow (but faster than doing it by hand). Once again, if I understood the failure mode, maybe I could avoid it. Hi, Out of interest, does it recalculate correctly if you do a full recalc (Ctrl-Alt-F9) as opposed to a quick recalc (F9)? Alan. -- The views expressed are my own, and not those of my employer or anyone else associated with me. My current valid email address is: This is valid as is. It is not munged, or altered at all. It will be valid for AT LEAST one month from the date of this post. If you are trying to contact me after that time, it MAY still be valid, but may also have been deactivated due to spam. If so, and you want to contact me by email, try searching for a more recent post by me to find my current email address |
#6
|
|||
|
|||
Hi Steve,
Might be a good idea to make a copy of the sheet or a backup of file first, to protect your data, and so that you can reexamine the original data. I would suggest running the TRIMALL macro to see if that fixes fixes things up. http://www.mvps.org/dmcritchie/excel/join.htm#trimall Will fix up a date that has a leading space, because the space will be trimmed, similar fixup for other numbers that were hidden because of a leading space. Will convert CHAR(160) or non-breaking space to a CHAR(32) space which if leading or trailing would also get trimmed off. Macro turns calculation on when finished, have just added a MsgBox indication if calculation was found to be off when macro was invoked. There are quite a few things that can cause problems that are noted with the description and in the section(s) above that reference. You can run the following tests on your original data to see what the problem actually was: The use of SUM (in your formula) for instance will only add up valid data, it will ignore text entries. One of the tests is an ISTEXT test. =ISTEXT(A1) which helps to identify problems, you could use Conditional Formatting as an initial vehicle to identify text and then to check each character of a cell to find out what the problem actually is. =CODE(MID(A1,1,1) =CODE(MID(A1,2,1) etc., if you want to know the reasons for problems before fixed up with TRIMALL. -- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "KiwiSteve" wrote in message ... Thanks to both Dave and David for the two replies. [...] As an aside I have seen many times a similiar problem with data we import into XL from external sources. Even though a cell contains a numeric value and is formatted as numeric, XL will complain that the cell has an error and won't calculate mathmatical results correctly until we do the 'no change' edit. |
#7
|
|||
|
|||
Thanks for the reply.
The following macros have all been run with the following results. Trimall had no affect ReEnter had no affect ReEnterF2 does fix it. I haven't had time as yet to check for the non printing character issue, but the tips there are appreciated as I am still baffled by the nature of the failure mode. I'll try and use these tips next week to confirm that the cells do or do not contain only valid chars. |
#8
|
|||
|
|||
"KiwiSteve" wrote in message
ups.com Thanks for the reply. The following macros have all been run with the following results. Trimall had no affect ReEnter had no affect ReEnterF2 does fix it. I haven't had time as yet to check for the non printing character issue, but the tips there are appreciated as I am still baffled by the nature of the failure mode. I'll try and use these tips next week to confirm that the cells do or do not contain only valid chars. Did you check whether a full re-calc does it? Could be a dependency table issue (I wonder if there is a maximum size to that table in the specs anywhere?) -- The views expressed are my own, and not those of my employer or anyone else associated with me. My current valid email address is: This is valid as is. It is not munged, or altered at all. It will be valid for AT LEAST one month from the date of this post. If you are trying to contact me after that time, it MAY still be valid, but may also have been deactivated due to spam. If so, and you want to contact me by email, try searching for a more recent post by me to find my current email address |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
adding row to forumla | Excel Discussion (Misc queries) | |||
IF / VLOOKUP formula won't work until saved | Excel Worksheet Functions | |||
Formula checking multiple worksheets | Excel Worksheet Functions | |||
formula won't work | Excel Worksheet Functions | |||
formula won't work | Excel Worksheet Functions |