+ Reply to Thread
Results 1 to 4 of 4

Sort without messing up formulas

  1. #1
    Ruth
    Guest

    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. #2
    David McRitchie
    Guest

    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" <[email protected]> wrote in message news:[email protected]...
    > 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. #3
    Ruth
    Guest

    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" <[email protected]> wrote in message news:[email protected]...
    > > 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. #4
    David McRitchie
    Guest

    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" <[email protected]> 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" <[email protected]> wrote in message news:[email protected]...
    > > > 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

    > >
    > >
    > >




+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Bookmarks

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