Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to update/change cell references in a formula | Excel Discussion (Misc queries) | |||
Auto Update Cell (Row) References | Excel Programming | |||
Cell references do not update | Excel Discussion (Misc queries) | |||
Copying formulas to other cells. Keeping references w/o $ sign. | Excel Discussion (Misc queries) | |||
Copying formulas to other cells. Keeping references w/o $ sign. | Excel Discussion (Misc queries) |