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.
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.
Omar:
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.
15 May 2005, 7:15 pmEric W. Bachtal:
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)).
15 May 2005, 9:58 pmKevin:
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.
16 May 2005, 5:38 amKevin:
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?)
16 May 2005, 5:45 amjkpieterse:
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.
16 May 2005, 6:27 amdoco:
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…
16 May 2005, 7:31 amJason:
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.
16 May 2005, 11:05 amRob van Gelder:
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)
16 May 2005, 2:25 pmJim T.:
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.
19 May 2005, 11:10 amearl:
Is it possible to sort 1 sheet based on the column of another sheet?
thanks
earl
9 January 2007, 3:07 pmRobert:
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
12 October 2007, 2:32 amRob:
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!!!
1 February 2008, 11:07 am