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.