Sorting Oddity/Bug

Category: General Weirdness | [Item URL]

This is odd -- and I'd say that it's a bug. I tried in Excel 2003 and Excel 2007, and I get the same result.

The figure below shows data and formulas entered into three columns. The worksheet is in formula view mode, so you can see the formulas.

Here's how it looks in normal view:

And here's how it looks after performing an ascending sort on column A:

Most would agree that the formulas in Column C should behave exactly like the formulas in Column B. The only difference is that the Column C formulas have a (seemingly superfluous) worksheet qualifier. As you can see, the formulas in Column C continue to point to the original cell.

This odd behavior could could lead to problems if you're not paying attention. In actual practice, however, it's very rare to have sheet-qualified references that refer to the same sheet that contains the formula. And, based on this simple example, it's probably fair to conclude that using sheet-qualified references on the same sheet is a bad practice.

Contributed by Bob Umlas.