References and Sorting (2003)

Topic

New Reply

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?

Reply | Quote

Replies

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.

Reply | Quote

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.

Reply | Quote

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.

??

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

Well, all I can say is . MS strikes again. Do you think they actually USE their own software?!?!?!

Appreciate the info guys.

Reply | Quote

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…

Reply | Quote

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.

Reply | Quote