Sort without messing up formulas

[Guest]

I have a spreadsheet where I would like to sort the results of a certain
column starting at the highest number down to the smallest number, when I do
this using the Z to A button, the last column (which works out a percentage
with a formula I have put in using the cells I wish to sort) gets messed up
and just shows #DIV/0! I think this maybe because it is automatically sorting
the total of the sorted column at the top of the spreadsheet after a sort
instead of the bottom which is in turn messing up where the formula is
looking to work out the percent.
Hope this makes sense and someone can help
Ruth

 

[David McRitchie]

Hi Ruth,
What do your formulas look like, perhaps use of OFFSET Worksheet Function
http://www.mvps.org/dmcritchie/excel/offset.htm
or perhaps you are sorting a row you should not be sorting.-

 

[Guest]

Yes you were right I was sorting the totals row too and putting it at the top
as the largest figure in the column, which in turn messed up the formula
which was pointing to the total row. I have fixed it by putting a blank row
between the last row of figures and the totals row, cos excel kept including
the totals row automatically but with a blank row it stopped doing that.
Thanks for your help
Ruth

 

[David McRitchie]

Hi Ruth,
Thanks for the verification.

You do not need a blank row before the total, that is why I included
a reference to
http://www.mvps.org/dmcritchie/excel/offset.htm

Or maybe you are using that blank row to separate the totals so you
can use the current region Ctrl+Shift+* by selecting only a single
cell -- the practice of sorting from a single cell selection is eventually going to
mess up a spreadsheet big time if you have an empty column.
http://www.mvps.org/dmcritchie/excel/sorting.htm
l