# Sort without messing up formulas

1. ## Sort without messing up formulas

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

2. ## Re: Sort without messing up formulas

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.-
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Ruth" <Ruth@discussions.microsoft.com> wrote in message news:0F9081BB-492B-496A-BF1D-3F59328F50CF@microsoft.com...
> 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

3. ## Re: Sort without messing up formulas

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" wrote:

> 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.-
> ---
> HTH,
> David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
> My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
> Search Page: http://www.mvps.org/dmcritchie/excel/search.htm
>
> "Ruth" <Ruth@discussions.microsoft.com> wrote in message news:0F9081BB-492B-496A-BF1D-3F59328F50CF@microsoft.com...
> > 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

>
>
>

4. ## Re: Sort without messing up formulas

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---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Ruth" <Ruth@discussions.microsoft.com> wrote...
> 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" wrote:
>
> > 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.-
> > ---
> > HTH,
> > David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
> > My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
> > Search Page: http://www.mvps.org/dmcritchie/excel/search.htm
> >
> > "Ruth" <Ruth@discussions.microsoft.com> wrote in message news:0F9081BB-492B-496A-BF1D-3F59328F50CF@microsoft.com...
> > > 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

> >
> >
> >

#### Thread Information

##### Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1