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 |
#9
|
|||
|
|||
Sorry, should have told you in the last reply, but full recalc didn't
do anything to fix it. At the moment the only action that fixes it reliably is either a manual 'no change' edit or the ReEnterF2 macro or my own macro which effectively does the same edit in a non destructive but far less efficient manner than ReEnterF2. I will try and use the non printing characater checks next week, but I must admit I find it hard to shake the mindset that XL is internally losing track of the data type of the cell (or something like that) until after an edit. Having said that, I am trying to keep an open mind and appreciate your continued answers. |
#10
|
|||
|
|||
Formula do not work until edited
I thought I'd bump this one back up in case anyone has had any more thoughts
on it.. I've got a similar problem but with dates. I'm running a macro to import some data to two worksheets (one a copy of the other) on the first sheet it recognises the dates and a formula tells me what the newest date is. On the other, the dates are not recognised until I do the non-destructive edit.. All the formats are the same, auto calc is on.. any ideas? |
#11
|
|||
|
|||
Formula do not work until edited
"CBMuteham" wrote in message
I thought I'd bump this one back up in case anyone has had any more thoughts on it.. I've got a similar problem but with dates. I'm running a macro to import some data to two worksheets (one a copy of the other) on the first sheet it recognises the dates and a formula tells me what the newest date is. On the other, the dates are not recognised until I do the non-destructive edit.. All the formats are the same, auto calc is on.. any ideas? Hi, Another possible solution: Could you enter something on the end of your formula that returns the latest date to make the formula volatile? Something like this perhaps: Current formula (where A1:A100 contains the date values): =MAX(A1:A100) Try this: =MAX(A1:A100)+(Now()-Now()) Now() being a volatile function it re-calcs every time the workbook re-calcs (e.g. pressing F9 should then work). HTH, 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 |
#12
|
|||
|
|||
Formula do not work until edited
"Alan" wrote: "CBMuteham" wrote in message I thought I'd bump this one back up in case anyone has had any more thoughts on it.. I've got a similar problem but with dates. I'm running a macro to import some data to two worksheets (one a copy of the other) on the first sheet it recognises the dates and a formula tells me what the newest date is. On the other, the dates are not recognised until I do the non-destructive edit.. All the formats are the same, auto calc is on.. any ideas? Hi, Another possible solution: Could you enter something on the end of your formula that returns the latest date to make the formula volatile? Something like this perhaps: Current formula (where A1:A100 contains the date values): =MAX(A1:A100) Try this: =MAX(A1:A100)+(Now()-Now()) Now() being a volatile function it re-calcs every time the workbook re-calcs (e.g. pressing F9 should then work). HTH, Alan. On re-reading the thread, my post is a little misleading.. the formula itself works. It's the dates that have been imported that are not being recognised until you click in the edit bar.. All the formating is exactly the same from one sheet to the other (they're copies of each other) yet one set of dates is recognised and the other isn't.. The same piece of code is used to import the data and the data comes from the same source.. I'm going to try re-creating the whole workbook to see if that solves the problem.. |
#13
|
|||
|
|||
Formula do not work until edited
Format the column as a date then use the TRIMALL macro
http://www.mvps.org/dmcritchie/excel/join.htm#trimall on the column. If that does not work then check out the content of the date reading the information that is with the description of the macro. Check if the value is a number or text, if it is text then check each character. --- 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 "CBMuteham" wrote in message ... "Alan" wrote: "CBMuteham" wrote in message I thought I'd bump this one back up in case anyone has had any more thoughts on it.. I've got a similar problem but with dates. I'm running a macro to import some data to two worksheets (one a copy of the other) on the first sheet it recognises the dates and a formula tells me what the newest date is. On the other, the dates are not recognised until I do the non-destructive edit.. All the formats are the same, auto calc is on.. any ideas? Hi, Another possible solution: Could you enter something on the end of your formula that returns the latest date to make the formula volatile? Something like this perhaps: Current formula (where A1:A100 contains the date values): =MAX(A1:A100) Try this: =MAX(A1:A100)+(Now()-Now()) Now() being a volatile function it re-calcs every time the workbook re-calcs (e.g. pressing F9 should then work). HTH, Alan. On re-reading the thread, my post is a little misleading.. the formula itself works. It's the dates that have been imported that are not being recognised until you click in the edit bar.. All the formating is exactly the same from one sheet to the other (they're copies of each other) yet one set of dates is recognised and the other isn't.. The same piece of code is used to import the data and the data comes from the same source.. I'm going to try re-creating the whole workbook to see if that solves the problem.. |
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 |