Sorting Formulas with Sheet Names

Jim T. sends this along, and I’m not sure what’s causing this behavior. Maybe you know and comment on it.

Col. D and Col. E are identical except that Col. E’s formula has range references that include the sheet name.

unsorted excel ranges

If you sort Col. D without sorting any other data on the sheet, you get the expected result (at least the result that I expect). That is, nothing happens. The formulas move to a different location and their range references change relatively. The formula that references A2, moves to D11 and then references A11. It looks like nothing happened.

Why then when I sort Col. E, does the same thing not happen? It sorts based on the values, but the formulas don’t change. Or maybe they do change – however you want to look at it.

excel ranges after sorting

Posted in Uncategorized

18 thoughts on “Sorting Formulas with Sheet Names

  1. I’m not sure what’s happening either, but this speaks to a problem I’m having in one of my sheets. It’s too complicated to explain quickly, so I won’t. Let’s just say this exercise may help me understand it.

    If you insert a column between D and E, you get the expected result on the new column F. I think that the sheet names cause Excel to think that column is a separate table, but its proximity to the other table causes a later part of the sort routine to re-sort the formulas as if it is all one table. Adding the buffer column forces Excel to treat the right column as a separate table.

    Intriguing.

  2. It’s like the non-sheet-qualified formula cells are sorted using copy/paste behavior (i.e., with relative reference rewriting), but the sheet-qualified formula cells are sorted using move behavior (i.e., address-literal, no relative reference rewriting), with the end result being formulas that are treated as if their rows were absolute (i.e., (A$2+B$2)*C$2)).

  3. How did you create your sheet? I tried just keying in the same set of items and formulae (Excel 2003) and can’t reproduce the behavior you’ve described.

  4. Ughh. Never mind the previous comment. I was sorting Descending instead of ascending for some reason (it’s early on a Monday, what can I say?)

  5. Hey, though it is weird, it is worth remembering. I cannot think of a situation where this may come in handy, but who knows!

    Sounds like a bug to me though.

  6. I am using Excel 2000 Premium 9.0.6926 SP-3

    I cannot reproduce the problem you speak of. It works as expected both ascending and descending. However, if I hand type the formula in Sheet2 and hit enter I get #NAME error, which is kinda strange…

  7. This is a known issue (in all versions of Excel, I think). You only get those same-sheet references in a formula if you write a formula using your mouse that points to another sheet first. For example, you might write/click

    =match(sheet2!A:A,sheet1!A1,0)

    That sheet1 reference happens automatically which doesn’t seem like a bad thing – until you try to sort! The sheet1 reference in the formula gets treated like an absolute reference. Best practice is to clean up same-sheet formula elements of this type.

  8. This problem could show itself if you build formula strings using references returned from RefEdit controls.
    They tend to attach sheet names to references.

    Something like this would solve:

    Private Sub refGetAddress_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    If IsRange(refGetAddress.Text) Then _
    If Range(refGetAddress.Text).Worksheet Is ActiveSheet Then _
    refGetAddress = Range(refGetAddress.Text).Address
    End Sub

    ActiveSheet is an assumption – if you know the destination sheet up front then use that.

    (in this example, IsRange is a function which returns boolean if the parameter looks like a range)

  9. Just to make you really shake your head. Sort Column E using A->Z… Nothing Happens. Now use Z->A and the results resort themselves. Use Z->A agan and they resort themselves back again. I have not found a legitimate use for this. More it is something to watch out for. I found it in a spreadsheet created by a coworker who did not know that anything was wrong.

  10. Thanks Jason. This was the answer/solution that I was looking for. I had this problem when using the index function, and must have created the sheet referenced links while clicking back and forth between the sheets that are referenced in the formula. I consider this a bug. The cell references aren’t absolute, so they shouldn’t be treated as absolute references. Note, I’m using excel 2002 which doesn’t have the same filtering capabilities as excel 2003, so not sure if this problem applies to both versions – but I imagine it does. Cheers, Robert

  11. bump!

    I’ve got a use for this! So i’m thinking it’s a UDF (undocumented feature)

    The use – I’ve got a pivot table which contains data I’m using in a formula (user-defined) in cells next to the pivot. I want to be able to sort the pivot table using the result of the formula but you can’t sort a pivot table using something not in the pivot table.

    In order to sort it then, I’ve linked other cells to the values in the pivot table, used these to calculate the formula and then I can sort…? Nope – because all the references are relative so when they’re sorted, they just update to there new place and so don’t appear changed. BUT, if you use the sheet-qualified references, the references don’t update and the data is sorted as required.

    So 2.5 yrs after the original post – a use for it!!!

  12. I’m glad I Googled this topic and found you. I have had this problem for years, and have finally decided the best answer is to get the formula results, and copy/paste special/values and then sort to get the desired result. I usually copy and paste the formula back in after the sort.
    I agree it’s a bug they need to fix! BTW if it matters I use version 2007

  13. I’m coming across this 2 years after the last post but this is still an issue in 2007 and 2010 and I think it happens a lot more than folks think…a simple exercise like doing a plain sumif on sheet1 when the data is on sheet2 will generate a same-sheet reference which I bet most folks won’t bother with assuming that it is relative. Seems like a huge friggin bug or feature with really unintended consequences.

  14. I spend two hours tried to figure this out and I am glad to find this post so that I don’t have to waste more time on such an aged bug. As pointed out by Andrey and Page, it is a absolute reference once you put the sheet name in. Same situation will occur if you are working on multiple tabs, let’s say you put a formula like “=sheet2!A1” in sheet 1, and sort sheet2, your result will be off once the original sheet2 A1 moved to somewhere else.

  15. Glad I found this today 11 years after originally posted!

    This ‘feature’ had us stumped as to what was happening with a set of data when trying to sort it as the data appeared not to be getting sorted. It turned out the column we were trying to sort on referenced another column, which referenced others, and just a few of the columns included the sheet name in the formula. So, while the column we were sorting was fine, the result wasn’t because some of the references were now pointing to “random” rows in the data instead of the relative row.

    Yuck. This is an awful gotcha!

    I see no excuse for this ‘feature’ to still persist after many years, especially as as far as I can see, there is no mention in the documentation that using a sheet reference makes a formula absolute when the data is sorted. Its completely unintuitive. If I want absolute references then I use the $ format to show I want the references to be absolute. I don’t want them to be implied to be absolute just because the formula happens to reference a sheet name. Sure, if the reference is outside of the range being sorted, then I can see how I might expect such a reference to be treated as an absolute, but if the reference is to the same sheet and in the range being sorted, I expect the reference to be amended acordingly so the calculations still refer to the same row as the rest of the data.

    OK – only option seems to be manually removing Sheet names from all formula if the sheet name is the same as the current sheet – or separating any such formula out of the range to be sorted (and if you need to sort on it, add a ‘kludge’ column which references the col to be sorted on and use that in the sort range, not the original with the sheet name in it.

    What a mess.

    Bascially – a good reason to never user sheet names in formula and to use tables/named ranges etc wherever possible.


Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.