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
Contact Forum Editor

Send an email to our Forum Editor:


PLEASE NOTE: Your name is used only to let the Forum Editor know who sent the message. Both your name and email address will not be used for any other purpose.

Tech Helproom


It's free to register, to post a question or to start / join a discussion


 

Excel Formulas moving (or not)


Dirty Dick
Resolved

Likes # 0

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

Like this post
wee eddie

Likes # 0

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.

Like this post
Dirty Dick

Likes # 0

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-)

Like this post
Belatucadrus

Likes # 0

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.

Like this post
TonyV

Likes # 0

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

Like this post
Dirty Dick

Likes # 0

Belatucadrua No blank cells on worksheet

TonyV

Forgot to say, I'm using Excel 2010

Like this post
TonyV

Likes # 0

Dirty Dick

I think you are left to VOG™ then!

TonyV

Like this post
VoG II

Likes # 0

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?

Like this post
lotvic

Likes # 0

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

Like this post
Sea Urchin

Likes # 0

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

Like this post
Dirty Dick

Likes # 0

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 ?

Like this post

Reply to this topic

This thread has been locked.



IDG UK Sites

OnePlus Two release date rumours: Something's happening on 22 July

IDG UK Sites

13in MacBook Air review, Apple's MacBook Air 2014 reviewed

IDG UK Sites

5 reasons to buy an electric car and 5 reasons not to

IDG UK Sites

Evernote Skitch: the best way for creatives to doodle feedback