Results 1 to 3 of 3
  1. #1
    Star Lounger
    Join Date
    Feb 2005
    Posts
    86
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Sort by value within a formula (2002 (SP3))

    Hi,
    I am always copying a pasting (values only) so that I can sort my data. Is there a simpler way to sort by value if my cells contain a formula?

    Thanks,
    -Alex

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Sort by value within a formula (2002 (SP3))

    It depends on the situation.

    If you have a table with data and an adjacent column with formulas that perform a row-based calculation, you can sort the table on the formula column. The other columns will be sorted together with the formulas.

    But if you have an isolated column with formulas, sorting will have no visible effect - the cells do get sorted but Excel adjusts the relative references so that the formulas end up as they were.

    There are two workarounds:

    1) Use absolute cell references instead of relative cell references. This is a nuisance because you cannot fill down formulas, you have to edit them manually or write a macro to create the formulas.

    2) Include the sheet name in the cell references, even when referring to cells on the same sheet.

  3. #3
    Star Lounger
    Join Date
    Feb 2005
    Posts
    86
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sort by value within a formula (2002 (SP3))

    (Edited by HansV to make URL clickable - see <!help=19>Help 19<!/help>)

    Hans,

    As always, great answer.

    I went with solution 2 and indeed it works now.

    For the record, I couldn't find a function in ASAP Utilities to insert sheet name in cell references, so I had to google it. The first thing I found, for the record:
    http://sam308.com/html/convert_cell_...es_utilit.html

    Again, thanks.
    -ALex

Posting Permissions

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