Welcome to Live View – Take the tour to learn more
Start Tour
There is currently 1 person viewing this thread.
Many hundreds of times over decades I have been able to DRAG formulae from cell to cell down a column e.g. to see the effect of compounding..
e.g. =(F300-F299)*1.1 for 10% growth. copied down calculates (F301-F300)*1.1 then (F302-F301)*1.1 ad infinitum.
For some reason, from yesterday, it no longer changes the formula correctly - but just copies the RESULT of the first calculation identically into every other cell. The only way to do it now is entering the correct formula into EACH cell individually.. which is okay for a few but crazy for dozens or hundreds.
How can I get EXCEL to work correctly again please?
e.g. =(F300-F299)*1.1 for 10% growth. copied down calculates (F301-F300)*1.1 then (F302-F301)*1.1 ad infinitum.
For some reason, from yesterday, it no longer changes the formula correctly - but just copies the RESULT of the first calculation identically into every other cell. The only way to do it now is entering the correct formula into EACH cell individually.. which is okay for a few but crazy for dozens or hundreds.
How can I get EXCEL to work correctly again please?
Show More
sorry if i don't understand question fully but is the original cell you are copying from an absolute value or a formula?
Is it more complex than copy, select target cells, right click, paste special, paste formula?
Is it more complex than copy, select target cells, right click, paste special, paste formula?
You have somehow switched off manual updating of formulas. Go to file, options, formulas and make sure automatic is checked.
Imagine, in example above, cell F299 had the value 100.. (the initial/original cell has a value yes)
entering =F299*1.1 in cell F300 produces the result 110 (obviously).
Just like creating a series of simple numbers or dates or any other you can, normally, pickup the bottom right corner of the previous cell and drag it down a column (or across a row) and EXCEL 'knows' to put an amended formula into the next cell and on and on.. Copy and pasting =F299*1.1 in other cells would only produce the same '110' result on every other cell.. which is NOT my goal.
Does that help you?
I'm now going to try your 'paste special' suggestion - which I never, ever. had to use previously TY.
entering =F299*1.1 in cell F300 produces the result 110 (obviously).
Just like creating a series of simple numbers or dates or any other you can, normally, pickup the bottom right corner of the previous cell and drag it down a column (or across a row) and EXCEL 'knows' to put an amended formula into the next cell and on and on.. Copy and pasting =F299*1.1 in other cells would only produce the same '110' result on every other cell.. which is NOT my goal.
Does that help you?
I'm now going to try your 'paste special' suggestion - which I never, ever. had to use previously TY.
No, 'paste special' doesn't help either.
Somehow my excel software seems to have been 'corrupted'!
I'm considering saving the sheet as a different (older) version and see if that helps.
Somehow my excel software seems to have been 'corrupted'!
I'm considering saving the sheet as a different (older) version and see if that helps.
HEY BARRY - You'se a GENIUS 
Job Done .. thank-you VERY much!!
Job Done .. thank-you VERY much!!
Cheers Barry!, very helpful that ( I presume you've created a few circular references in your time )
I'd never even notissed that 'options' button before - neat innit! :)
You drag down formulas? 
Here is a tip for you drag artistes.
Look at the black rectangle around the active cell.
Look at the bottom right corner of the active cell. It is different. There is a small black square there.
Now look at the cursor. It is a thick white cross.
Move it over the small black square at the bottom right of the active cell.
The thick white cross changes to a thin black cross.
While the thin black cross is visible double-click the mouse.
The contents of the active cell (formulas) are copied down until they meet the end of the list
Example
Jan ... 100
Feb ... 110
Here is a tip for you drag artistes.
Look at the black rectangle around the active cell.
Look at the bottom right corner of the active cell. It is different. There is a small black square there.
Now look at the cursor. It is a thick white cross.
Move it over the small black square at the bottom right of the active cell.
The thick white cross changes to a thin black cross.
While the thin black cross is visible double-click the mouse.
The contents of the active cell (formulas) are copied down until they meet the end of the list
Example
Jan ... 100
Feb ... 110
Example
Jan 100
Feb 110
Mar
Apr
May
Jun
Jul
Aug
Sep
Oct
Nov
Dec
Year
Assuming the 110 in cell B2 is a formula based on the 100 (e.g. B1*1.1) then double clicking cell B2 will shoot the formula down to Dec
It stops there when it meet the blank cell between Dec and Year.
The number of man-years lost in the EU dragging down formulas is a scandal.
Atheists might not like the thick white cross and the thin black cross.
Jan 100
Feb 110
Mar
Apr
May
Jun
Jul
Aug
Sep
Oct
Nov
Dec
Year
Assuming the 110 in cell B2 is a formula based on the 100 (e.g. B1*1.1) then double clicking cell B2 will shoot the formula down to Dec
It stops there when it meet the blank cell between Dec and Year.
The number of man-years lost in the EU dragging down formulas is a scandal.
Atheists might not like the thick white cross and the thin black cross.
I am ATHEIST.. and you KNEW IT! GRRRR!
Cheers anyway.. I'll have a play with that kincsem TY!
Cheers anyway.. I'll have a play with that kincsem TY!
Post Your Reply
Please login to post a reply.
Wonder
Instance ID: 13539