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

The thing is, the formula you showed has absolute, not relative references in it.

Answer 2

Hi

The dollar sign signifies an absolute reference within a cell formula.

$C$14 says to ALWAYS refer to column C and ALWAYS refer to cell 14

Getting rid of the $ before 14 will turn it into a relative reference.

You can click into the formula within the $C$14 and press F4 to toggle through the various cell reference combinations so you don't have to type them.

Answer 3

If you want to sort an entire Sheet, based on a Particular Cell, choose the entire sheet Workbook to go to sort, and the first criteria Choose cell location column. You can choose A-Z or 10 to 9  or the reverse order. Just sorting on the cell without choosing the entire workbook or the area you want to sort, will mess up your work.  By the way, if you want to sort using multiple criteria you want to put most important as third, next most important second, and final as number one. Say you were sorting by names:

First would choose the Last name first in the first sort. Name in the second sort.

__________

Disclaimer:

The questions, discussions, opinions, replies & answers I create, are solely mine and mine alone and do not reflect upon my position as a Volunteer Moderator.

Answer 4

Here is the formula:

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

'Error Demo' is the local tab when the SORT is done. Are you saying 'Error Demo'!C10 is an absolute reference? It looks relative to me - right?

Why would Excel treat this 'Error Demo'!C10 as absolute?

Answer 5

Hi

Your cell formulas use absolute, not relative references for row numbers. Try changing the row number references to relative, rather than absolute references.