Excel Formulas moving (or not)

  Dirty Dick 15:17 06 Oct 11

I have a spreadsheet that has the dates in Column A. When I sort the entries into date order, the formulas in that line (1,2,3 etc.) don’t follow the date.

What am I doing wrong ?



  wee eddie 15:45 06 Oct 11

My guess: Highlight the whole Selected area you want to Sort or Click the Top left-hand Corner to select the whole sheet > Select Sort from the Tool Bar > Select Column A as the Basis.

If this does not work, wait for Vog, he's the master of such things.

  Dirty Dick 15:54 06 Oct 11

Thanx wee eddie

I have highlighted all the cells I want to sort, and selected the "date" column.

Come on Vog, where are you ?


  Belatucadrus 15:54 06 Oct 11

Do you have a blank cell between the date and the line formulas ? When in a block it should assume that the date sets are related, a blank could confuse it and you may need to highlight all the relevant data before sorting.

  TonyV 16:13 06 Oct 11

Dirty Dick

I have Version 2007 and by selecting all the cells involved, Right Click and then Sort, then Oldest to Newest and it worked fine. Equally so I tried with a column blank before the data but after the date and that also sorted correctly by using the method I have outlined.

Hope you get it sorted.


  Dirty Dick 16:39 06 Oct 11

Belatucadrua No blank cells on worksheet


Forgot to say, I'm using Excel 2010

  TonyV 16:53 06 Oct 11

Dirty Dick

I think you are left to VOG™ then!


  VoG II 18:52 06 Oct 11

I can't replicate that behaviour.

Do you have $ signs in the formulas they may not sort as expected.

What are the formulas in the other columns?

  lotvic 22:37 06 Oct 11

Is it just that one spreadsheet that does not sort properly or have you got the same problem with all your spreadsheet workbooks?

  Sea Urchin 00:07 07 Oct 11

"I have highlighted all the cells I want to sort, and selected the "date" column".

Surely you need to highlight all the cells, and then select Sort - not select the date column?

  Dirty Dick 10:05 07 Oct 11

Sea Urchin I have highlighted all the cells that I want to sort , and then use the date column as the lead factor.

I'll try to explain a little more.

The workbook contains several sheet, one for each year. One of the colums is headed "Previos Year"which I have a formula in (='2010'!H6). The next column id headed "Forecar +10%". I use the formaula (=(D6*10/100) +D6) to calculate the +10% cost.

The next column is headed "Balance Forecast" which has the formula (=F5-E6) the F5 cell is the Balance brought forward.

I have 3 further colums headed "Credits" "Actual amount" and "Actual Balance" . The "credits" just adds on deposits into the bank as a figure, the Actual amount is the bill cost, as a figure and the "Actual balance" is a formula (=I5-H6) which subtracts the bill from the balance.

When I sort the cells (highlighting the columns A to J, I get "Value" errors" showing in some cells, and when I check the formulas they don't refer back to the correct cells.

Is there any way I can post the workbook so people can see what I mean ?


This thread is now locked and can not be replied to.

UK's best mobile network 2016: best 3G, best 4G, best mobile networks revealed

1995-2015: How technology has changed the world in 20 years

Google will stop accepting new Flash ads on June 30

How to use an Android Wear smartwatch with an iPhone: The benefits and drawbacks of mixing Android…