Tech Helproom
It's free to register, to post a question or to start / join a discussion
Excel Formulas moving (or not)
Likes # 0
Posted October 6, 2011 at 3:17PM
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
Likes # 0
Posted October 6, 2011 at 3:45PM
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.
Likes # 0
Posted October 6, 2011 at 3:54PM
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-)
Likes # 0
Posted October 6, 2011 at 3:54PM
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.
Likes # 0
Posted October 6, 2011 at 4:13PM
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
Likes # 0
Posted October 6, 2011 at 4:39PM
Belatucadrua No blank cells on worksheet
TonyV
Forgot to say, I'm using Excel 2010
Likes # 0
Posted October 6, 2011 at 4:53PM
Dirty Dick
I think you are left to VOG™ then!
TonyV
Likes # 0
Posted October 6, 2011 at 6:52PM
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?
Likes # 0
Posted October 6, 2011 at 10:37PM
Is it just that one spreadsheet that does not sort properly or have you got the same problem with all your spreadsheet workbooks?
Likes # 0
Posted October 7, 2011 at 12:07AM
"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?
Likes # 0
Posted October 7, 2011 at 10:05AM
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 ?
Reply to this topic
This thread has been locked.
Check out PC Advisor's other tech forums
Top 5 Most Popular
