Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3
Default Copying cells- the cell references don't update

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

  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 11,058
Default Copying cells- the cell references don't update

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.
--
Gary's Student
gsnu200702


"Sarahj" wrote:

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


  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3
Default Copying cells- the cell references don't update

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

Gary''s Student wrote:

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.
--
Gary's Student
gsnu200702


"Sarahj" wrote:

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



  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1,117
Default Copying cells- the cell references don't update

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


Sarahj wrote:
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

Gary''s Student wrote:

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.
--
Gary's Student
gsnu200702


"Sarahj" wrote:

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



  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 22,906
Default Copying cells- the cell references don't update

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

On 21 Jan 2007 09:33:51 -0800, "Sarahj" wrote:

Can anyone throw any light
on how the setting had become changed to Manual? I certainly did not
consciously change it.




  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3
Default Copying cells- the cell references don't update

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.
Gord Dibben wrote:

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

On 21 Jan 2007 09:33:51 -0800, "Sarahj" wrote:

Can anyone throw any light
on how the setting had become changed to Manual? I certainly did not
consciously change it.


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How to update/change cell references in a formula 510 Financeguy Excel Discussion (Misc queries) 2 October 7th 08 05:27 PM
Auto Update Cell (Row) References joecrabtree Excel Programming 3 December 8th 06 02:42 PM
Cell references do not update ivan Excel Discussion (Misc queries) 4 October 28th 06 10:34 AM
Copying formulas to other cells. Keeping references w/o $ sign. GregP1962 Excel Discussion (Misc queries) 10 April 22nd 06 03:11 AM
Copying formulas to other cells. Keeping references w/o $ sign. GregP1962 Excel Discussion (Misc queries) 3 April 21st 06 07:24 PM


All times are GMT +1. The time now is 03:28 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"