Excel - Copy and Paste in Excel, copies cell and formula, but shows same v

Asked By ipwil on 26-Jan-10 08:26 AM
Excel 2003 with 2007 converters installed.  Not constant problem, but have
seen it once before, cannot remember how to stop it happening.

I have a bank forecast which I have a running total column, against a
individual line item list. Noddy stuff.

Problem. If I make changes to the forecast I need to recopy down the running
total formula to refresh the running total when it does not pick up the
additions, usual when a cut has been pasted in.  Whilst it allows me to
dragdown or copy and paste the formula and the new cells take on the correct
line/column numbers to do their totalling all that shows is the actual VALUE
of the original copied cell and ignores the formulas within.

Any ideas?


how it should work
10   10
15   25 (cell above add cell to the left)
12   37 (cell above add cell to the left)
13   50 (cell above add cell to the left)

what happens now when I copy down the formula in the first 25 total
10   10
15   25 (cell above add cell to the left)
12   25 (cell above add cell to the left)
13   25 (cell above add cell to the left)

the formulas are correct in the cells and the cells are formated to numbers

Dave Peterson replied to ipwil on 26-Jan-10 08:53 AM
Make sure that calculation is set to automatic:

Tools|Options|calculation tab


Dave Peterson
ipwil replied to ipwil on 26-Jan-10 09:06 AM
I have the answer, but I shall leave the post up for a while so others can
see the reason should it have happened to them.

HOW it could have happened I do not know BUT Tools - Options - Calculations
Tab - Manual vs Automatic Calculations (Change to Automatic)

It was in Manual with F9 to refresh calculations, but I could not have
keystoked that setting!? and certainly did not change it in the menu.
Don Guillett replied to ipwil on 26-Jan-10 09:13 AM
Excel will change based on the first file opened.

Don Guillett
Microsoft MVP Excel
SalesAid Software