Excel Sort Command not working AT ALL as expected

Question

I have a table in excel (formatted as a Table), that does allow me to sort, but its results are incorrect in at least two ways:

1. the numeric value column I am sorting on A-Z or Highest-to-lowest comes back NOT in order (and I cannot seem to make any pattern fit to what it actually is doing?!?

   

2. It is "jumbling" the other column's data haphazardly. (Like what you would expect if you had NULLS or hadn't selected the entire data range.).

• I do have it formatted as a TABLE.
• I do not have any NULLS/blanks in the data range.
• I have tried selecting the entire table before sorting and tried not selecting the entire table as well.
• I have tried to make all the formulas either relative (either with $'s (as in $A$1), or with references to the Table columns (as in 1-[@[ColumnHeader]]).
• I do have some fairly complex formulas but it is a small table (27 rows by 11 columns) and they all work UNTIL I try to SORT.

Per comments, trying to share the entire table with an example of one of the formulas in the formula bar.
Yes, there is a formula in the "% Model" column upon which I am trying to sort, but I am trying to sort on the values...isn't that possible? entire table and one formula

Adding screen recording of formula text. enter image description hereenter image description hereenter image description hereenter image description hereenter image description here

Answer 1

In the setup you show, the Name column (column X) is populated with formulas referring to cells using normal references while you are "religious" in other formulas, it looks like, about using structured references when available. And in several of the formulas pictured, N41 and N43 figure prominently.

The implication of the above is that these cells lie outside the Table. Another indicator is the existence of the Name column itself. If column A were part of the Table, that whole column would not be necessary to the Excel work, though one could definitely like the names repeated periodically in a data set that spans several monitors-worth of real estate and one needs to always see the names. There are other ways to do that, but this would be one as well. However, given the references to N41 and N43, this column existing implies your Table start in column X.

What does this matter? Your chosen sorting column, AB, has a formula that draws a value from each row's Actuals column. It, in turn, has formulas that draw upon the Name column for a lookup that presumably returns some numerical value.

(Incidentally, in that formula, NOT FOUNT is used for the value to return if a name is not found. However, without double quotes on each end, it will be interpreted as a Named Range, but with the space between words, it cannot possibly BE a Named Range. Unless I'm missing something, that needs fixed and the only reason it hasn't been a problem is that, like a related issue with all those "NotTru" possibilities in other formulas, the data simply hasn't caused that part of the formula to be used, so has never tossed its monkey wrench into things. The "NotTru" issue, by the way, is that it is text, not a numerical value, in formulas that clearly must have numerical inputs. So if that were the result of the IF() it is in, the formulas would then be dividing by... text... and would produce an error.)

That numerical value is then used in your % of Model column's formula. So it all hinges back to the Names column. Since those are not structured references (presumably because column A is not part of this Table), Excel will do the Sort and for a few micro- or milli-seconds, the items in that column will move with their rows exactly as desired, then before you ever see that, Excel adjusts the not-structured-references formulas (=A7 and so on), to maintain their original order which has the knock-on effect of returning the Table to its pre-Sort appearance.

Fixing this will be a problem.

One way would be to reverse the looking up, sort of. Put the real name data in the Names column and in column A, put the formula to get that row's name. Of course, if there's other data not mirrored in the Table, it won't sort with the Table so... problem.

Another would be to revamp things. Place all the data in the Table rather than to its left. Columns with operating things like cells N41 and N43 would stay out of the Table. Perfectly doable but could cause a certain amount of grief in the operation if there are other users and they don't like that... problem.

The latter is probably really the only way to go.

There is no reason it can't go into the Table, in the Excel sense. Once in the Table, the Table need not look outside for data, so no formulas to interfere with Sorting. Operating cells like N41 and N43 do not belong to any particular record and stay outside the Table. Then one has a free hand in sorting and so on.

Answer 2

What you've got here is the equivalent of sorting formulas without having Excel move the underlying data around.

Imagine you had this:

     A       B
1    Data    Calc    (formula from column B)
2    1       2       =A2*2
3    5       10      =A3*2
4    8       16      =A4*2
5    0       0       =A5*2

And you had Excel sort column B -- only column B. It would move those formulas up and down based on the values in the cells, but those are relative references -- Excel will modify them when it moves them up and down.

E.g., B4, which equals 16, should be at the bottom. Excel will take the formula in B4 ('=A4*2') and do the equivalent of copying it and pasting it one cell down, in B5. And you know what Excel will do with a copy-paste like that; when it pastes the formula down one cell, it will update it to be '=A5*2'. Which means even after the sort, B5 will still be evaluating to zero. Basically, the sort won't do anything.

The way to fix this is to include column A in the same sort; you can sort on column B, and as long as the underlying data actually gets moved, you'll get what you want. Copying and pasting B4 AND A4 to B5 and A5 will move the 8 down to A5, and B5 will still end up with the formula '=A5*2', which is now what we want because now it's 16.

What you've got is the equivalent of this much simpler example -- effectively, you're doing the equivalent of just moving the formulas around without moving the underlying data. You need to attack that somehow.