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.

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

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" <[email protected]> wrote in message news:[email protected]...
> 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.
>

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.

"KiwiSteve" <[email protected]> wrote in message
news:[email protected]
> 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:

[email protected]

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

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" <[email protected]> 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.

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.

"KiwiSteve" <[email protected]> wrote in message
news:[email protected]
> 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:

[email protected]

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

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.

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?

"CBMuteham" <[email protected]> wrote in message
news:[email protected]
>
> 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:

[email protected]

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

"Alan" wrote:

> "CBMuteham" <[email protected]> wrote in message
> news:[email protected]
> >
> > 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..

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" <[email protected]> wrote in message news:[email protected]...
>
>
> "Alan" wrote:
>
> > "CBMuteham" <[email protected]> wrote in message
> > news:[email protected]
> > >
> > > 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..