Copying cells- the cell references don't update

[Sarahj]

I have some finance spreadsheets I have used for ages. I'm often doing
a calculation for one row and then copying it down for all the rows.
Suddenly instead of updating the cell references it merely copies the
contents of the first cell. AND when I point to one of the later cells
it gives the updated cell reference. ie the contents of the cell does
not reflect the reference indicated.
eg A1 contains 1, A2 contains 2 A3=A1+A2=3
B1 contains 4 B2 contains 5 Copy A3 down and B3 shows 3 but the
reference showing is B1+B2 which should be 9!
If I open Excel again a new worksheet behaves properly. However all my
old spreadsheets have developed this disturbing error.I'd be grateful
for an explanation.
Many thanks

 

[Gary''s Student]

If, in you work session, you open several different worksheets with the same
instance of Excel, Excel will use the Options of the first worksheet and
apply it to all the following sheets. So if you openned a worksheet that was
set to Manual mode, and then openned other worksheets, they would all be in
Manual mode.


To test if this is your problem, close all instances of excel, open Excel
with a blank worksheet, verify the mode is Automatic, and finally open any of
your old sheets.

 

[Sarahj]

Thank you for your help. Indeed the setting had become changed to
Manual and resetting to Automatic fixed it. Can anyone throw any light
on how the setting had become changed to Manual? I certainly did not
consciously change it.
Many thanks

 

[Susan]

sarahj -
mine changed once like that, too. i did not change it myself, cuz i
don't fool with that setting. i don't know how it changed itself to
manual, but it hasn't changed itself back since then.

i think PERHAPS that a sample workbook i had downloaded & tried out off
the web changed it & didn't change it back, but i can't say it for
sure. i download these fairly often to see examples of vba projects &
then decide if i want to save it or not.

susan

 

[Gord Dibben]

Sarah

Re-read the response from Gary's Student about the order in which you open
workbooks and what Calc Mode Excel defaults to.


Gord Dibben MS Excel MVP

 

[Sarahj]

I have never set a workbook to manual and can only assume I have
downloaded one that was set that way and that it reset all mine. This
seems dangerous, especially as the cells erroneously indicate that they
are the sum of the cells. (ie they indicate that the calculation has
been carried out by updating the cell references) There is no warning
that this is not so. I can hardly believe this can happen. So much for
telling my employees that to check their work just click on the cell
and it will tell you what cells have been used to obtain that figure.