Excel Formulas moving (or not)

  Dirty Dick 15:17 06 Oct 2011
Locked
Answered

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 ?

Thanx

DD

  wee eddie 15:45 06 Oct 2011

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 2011

Thanx wee eddie

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

Come on Vog, where are you ?

8-)

  Belatucadrus 15:54 06 Oct 2011

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 2011

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.

TonyV

  Dirty Dick 16:39 06 Oct 2011

Belatucadrua No blank cells on worksheet

TonyV

Forgot to say, I'm using Excel 2010

  TonyV 16:53 06 Oct 2011

Dirty Dick

I think you are left to VOG™ then!

TonyV

  VoG II 18:52 06 Oct 2011

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 2011

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 2011

"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 2011

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.

Elsewhere on IDG sites

Fujitsu Lifebook P727 laptop review

Microsoft Paint set to die after 32 years

Mac power user tips and hidden tricks

Comment désactiver la saisie intuitive et paramétrer votre clavier ?