Excel Formulas moving (or not)

  Dirty Dick 06 Oct 11
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 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 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 ?

8-)

  Belatucadrus 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 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.

TonyV

  Dirty Dick 06 Oct 11

Belatucadrua No blank cells on worksheet

TonyV

Forgot to say, I'm using Excel 2010

  TonyV 06 Oct 11

Dirty Dick

I think you are left to VOG™ then!

TonyV

  VoG II 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 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 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 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 ?

Advertisement

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

Should I upgrade to Windows 10? 8 reasons why you should upgrade to Windows 10... and 2 why you…

We are being sold the ability to spend money we don't have. And we love it

IKinema aims to banish droopy shoulders and wonky spines in animated CG characters

How to use Apple Music in the UK: Complete guide to Apple Music's features

We use cookies to provide you with a better experience. If you continue to use this site, we'll assume you're happy with this. Alternatively, click here to find out how to manage these cookies

hide cookie message