Excel messes up formula when using sort

Question

Can someone please help.   I am going around in circles with MS support.     Office version 15.0.4420.1017   Excel version 15.0.4535.1507

When I use sort, the formula in a cell which references another cell does not remain constant.   I would be grateful if someone could see for themselves:

Open a new sheet

In column A, Rows 1 to 9 add dates

1/2/2014

2/2/2014

to 10/2/2014

In column B, Rows 2 to 9.  Enter the number 1.

In Cell B,1   add this formula    =SUM(A15)

Now put the value 10 into cell A15

Cell B,1 should now also show the number 10

Now change the date in cell A1 to 11/2/14 and then simply highlight rows 1 to 9 and sort by column.   Watch the 10 change to a zero.

This is terrible for an software package designed to manipulate numbers!

Thanks to anyone who can help me with this.

Mark

Answer 1

1.  You don't need the SUM function.

2.  Use Absolute cell references   =$A$15  or  =A$15  entered in B1 then sort.

See Help on Absolute and Relative references.

Gord

Answer 2

"What have MS done to Excel. in 2013.  I think this is very dangerous!"

I don't have 2010 or 2013 but sorting was just as dangerous in earlier versions if you follow the instructions you gave in your original post.

Then again, maybe I did not follow your instructions properly.

Gord

Answer 3

HI Gord,

Thanks for taking time to respond.

My issue is that I have upgraded from 2010 to 2013 and I have a rather large spreadsheet full of these references.  in 2010 there were never any issues.  What have MS done to Excel. in 2013.  I think this is very dangerous!

Mark

Answer 4

HI Gord,

Thanks for taking time to respond.

My issue is that I have upgraded from 2010 to 2013 and I have a rather large spreadsheet full of these references.  in 2010 there were never any issues.  What have MS done to Excel. in 2013.  I think this is very dangerous!

Mark