Sort function introduces errors

Question

See excel file at this Dropbox link.

References in equations are not sorted properly if there is a TAB REF prefix to a cell local to the current tab.

Excel Bug.xlsx

Answer 1

Jim, please go volunteer somewhere else. You should try reading the thread before regurgitating canned, well documented material. I can get a tutorial on absolute v. relative references a thousand places.

I'm looking to point out a very specific anomaly related to relative references not being handled correctly by Excel resulting in unexpected results. I'm not going to reexplain it to you - please actually read the thread.

Answer 2

Hi JoeMarkee,

This is not a bug as the sort function is working as it is expected.

As I explained earlier, using 'Error Demo'! (Same Sheet) reference in formula causes excel to believe that the reference is taken from another sheet and should be moved to its new relative position after sort.

Is there any specific reason for using 'Error Demo'! (Same sheet) reference? The calculated values remain same even if it not used in the formula.

E Column - Formula move to new rows as you are using 'Error Demo' (same sheet ref).

F Column - Formula do not move to new rows as you are not using 'Error Demo' (same sheet) ref.

Thanks,

Neha

Answer 3

Ok, let me try again. See below, the SORT function introduces an ERROR into the E13 formula (post SORT).

'Error Demo'!C11 is not updated by SORT for the new Row location 13. The formula should be 'Error Demo'!C13as is shown in the control Column F shown at bottom.

All rows that are moved by SORT contain the same ERROR. Row 12 does not have an ERROR because SORT does not move it.

This is very clear - either this is a bug in Excel that should be fixed or else Excel SORT cannot be trusted to update cell references in formulas. If the later, then Microsoft should notify users that SORT results cannot be trusted.

Cell E11 before SORT

=DataTotal!$B$1*'Error Demo'!C11/'Error Demo'!$C$14

Cell E13 after SORT (Sort moves ROW 11 to ROW 13)

=DataTotal!$B$1*'Error Demo'!C11/'Error Demo'!$C$14

Cell F11 before SORT

=DataTotal!$B$1*C11/$C$14

Cell F13 after SORT (Row 11 -> Row13)

=DataTotal!$B$1*C13/$C$14

Answer 4

Oh really? Is that the best you can do?

I sort a table that calculates perfectly before the sort and then the calculations are WRONG after the sort. 

Is this the way the Excel SORT function is supposed to work? I DON'T THINK SO.

Can you a least tell me how to SORT this table so that the calculations remain correct?

This is a bug.

Answer 5

Hi JoeMarkee,

Sorry for the late reply.  I checked your workbook.

The formula in one of the columns has the reference of the same workbook. The worksheet reference is causing Excel to think that you are referring to C8 ('Error Demo'!C8) on a difference worksheet and it should not be changed relative to its new sorted position.  So once sorted this part of formula also moves along with 1 and it changes the values.

Thanks,

Neha