Results 1 to 9 of 9
  1. #1
    Star Lounger
    Join Date
    May 2002
    Location
    Fort Worth, Texas, USA
    Posts
    58
    Thanks
    0
    Thanked 0 Times in 0 Posts

    References and Sorting (2003)

    I have info on one sheet (call it "base") that I reference in other sheets. The cell references are relative and everything works fine until I add a line and do a sort on the base sheet. The reference seems to revert to an absolute reference -- i.e., the sort seems to blow its mind.

    If I just add or delete a line in the base sheet, no problem.

    Is this a known behavior? Any way to fix it, short of never sorting the base sheet? <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>
    Thanks - - - Sher

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

    Re: References and Sorting (2003)

    I don't have Excel 2003, but I've never seen this behavior in any of the earlier versions (up to and including Excel 2002), and I cannot find any mention of it. For the benefit of Loungers with Excel 2003, it might be helpful if you could attach a small workbook that exihibits this problem.

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: References and Sorting (2003)

    Hans: Here is a workbook that I created in XL2K. If I sort the range Sheet1!A1:A10 into ascending order, the values in range Sheet1!E1:E10 and the values in Sheet2!A1:A10 are also sorted. I would have expected that they would not be sorted.
    Legare Coleman

  4. #4
    Star Lounger
    Join Date
    May 2002
    Location
    Fort Worth, Texas, USA
    Posts
    58
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: References and Sorting (2003)

    Thanks Legare. Now add a value of 6.1 in line 11 of Sheet 1. Then sort Sheet 1. You'll see that the values in Sheet 2 don't maintan the original values as they should.

    ??
    Thanks - - - Sher

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

    Re: References and Sorting (2003)

    I see what you mean now. If you drag cells around, or copy and paste them, Excel will automatically adjust formulas referring to those cells, but if you sort a range, formulas referring to cells in the sorted range remain unchanged. I suppose it would be too complicated to keep track of what happens to all the cells.

  6. #6
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: References and Sorting (2003)

    I don't understand why it would be too complicated. If it can keep track of it when you insert/delete/drag/copy/paste, why can't it keep track when it sorts. Each time it moves a cell, adjust the formulas. If that is too slow, then take then take the precedent/dependent chain pointers with the cell, and do the update after all cells are moved.

    I first noticed this on XL95 with a workbook I had to do large updates to every week from 1995 to 1997. It gave me great grief. I ended up adding an extra key column in the range to be sorted and using VLOOKUPs instead of references. It was a royal PAIN.
    Legare Coleman

  7. #7
    Star Lounger
    Join Date
    May 2002
    Location
    Fort Worth, Texas, USA
    Posts
    58
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: References and Sorting (2003)

    Well, all I can say is <img src=/S/frown.gif border=0 alt=frown width=15 height=15> . MS strikes again. Do you think they actually USE their own software?!?!?!

    Appreciate the info guys.
    Thanks - - - Sher

  8. #8
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Ankeny, Iowa, USA
    Posts
    298
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: References and Sorting (2003)

    Is it possibly that they don't view sorting as actually physically *moving* the cells - more that they see it as changing the values in the cells? In that instance, I can see where they would differentiate between cutting/pasting a cell vs. sorting their values...

  9. #9
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: References and Sorting (2003)

    I think the issue is, that sorting is not "moving" the cells. Sorting is just "copying" the cells, so the cell links are not (and should not) be adjusted.

    Only If the sorting acted as a move would I expect to act as you suggest.

    If sorting did act like "moving" it could destroy ranges in Lookup and indexes. If your range were A1:A100 and you referenced it in an index, and the sort "moved" A1 (the first cell in your index) to A99 and kept A100 (the last cell in the range) in A100, your index would now refer to A99:A100 due to the sort (this is what would happen if you moved A1 to A99 with an index to A1:A100). This is not a behaviour that I would be happy with when I use indexes or even range names...

    Steve.

Posting Permissions

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