Results 1 to 15 of 16
Thread: Sorting Issue (2007)

20120126, 17:19 #1
 Join Date
 Jan 2001
 Location
 La Jolla, CA
 Posts
 1,222
 Thanks
 17
 Thanked 45 Times in 44 Posts
Sorting Issue (2007)
In a 2007 spreadsheet, my user has several dozen rows. Several of the columns have formulas.
Some formulas use a variety of nested formulas and an IF statement and a VLOOKUP within the IF.
When sorting the range of data from A1:G500 using column B (student semester) and within that column A (last name), at least one of the columns with a VLOOKUP inside an IF doesn't have the cell ref changed in the VLOOKUP. Consequently, the VLOOKUP is looking at the original row value and not the new, sorted row value.
What's going wrong?
Thanks in advance.

20120126, 19:48 #2
 Join Date
 Mar 2004
 Location
 Manning, South Carolina
 Posts
 7,892
 Thanks
 281
 Thanked 1,118 Times in 1,023 Posts
K,
Is the reference either a fixed reference, i.e. contains a $ or is it a Named Range {also a fixed reference}?

20120127, 12:09 #3
 Join Date
 Jan 2001
 Location
 La Jolla, CA
 Posts
 1,222
 Thanks
 17
 Thanked 45 Times in 44 Posts
Sorting Issue
It's a straight cell ref as in =VLOOKUP(G12,range,false)
When the full range of data is sorted per the original post, the G12 hasn't changed relative to the sort.
Specifics: here's the formula in I12.
=IF(ISNUMBER(F12),ROUND(F12*VLOOKUP('MASTER GRADE SHEET'!G12,'Course Codes'!$A$4:$D$1000,4,FALSE)/113,0),"NH")
If I sort over column column D (numerical increasing), this is what happens to row 12:
=IF(ISNUMBER(F12),ROUND(F12*VLOOKUP('MASTER GRADE SHEET'!G13,'Course Codes'!$A$4:$D$1000,4,FALSE)/113,0),"NH")Last edited by kweaver; 20120127 at 18:33.

20120128, 05:51 #4
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Are you sure that is the change? I would have expected if the F12's stayed that the G12 would not change unless one is locked...
I notice that you seem to have 2 separate worksheets reference. Is the issue that they do not sort together?
Steve

20120128, 10:49 #5
 Join Date
 Jan 2001
 Location
 La Jolla, CA
 Posts
 1,222
 Thanks
 17
 Thanked 45 Times in 44 Posts
That's really what happened. I copied the code, then did the sort and copied the resulting code. Very confused.

20120128, 14:39 #6
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
I am surprised I just tested it. The references to the current sheet, go like they are copied, but on the external sheets, they act as if they are "moved" and they get sorted as well. Since they are formulas and don't really need to be sorted (and you don't want them sorted the way excel sorts them) is to NOT include this column in the sort range.
Can anyone confirm: Is this a new feature with XL2007/2010? I never noticed this behaviour in earlier versions...
Steve

20120130, 11:20 #7
 Join Date
 Jan 2001
 Location
 La Jolla, CA
 Posts
 1,222
 Thanks
 17
 Thanked 45 Times in 44 Posts
Sorting Issue
I'd like to be able to bypass this one column in the sort, but selecting the first several columns and then selecting other nonadjacent columns using CTRL doesn't work for the sorting option. GROANING.

20120130, 17:45 #8
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
I had a chance at work today to examine an older computer with an earlier version of excel (XL97). This version does have the same behaviour. If the formula references a cell IMPLICITLY (there is no explicit worksheet listed, meaning it is assumed to be on the active sheet) then that reference, when sorted becomes a relative reference (like it is copied). For example, if the formula starts in A12 and references J12, and after sorting the formula gets put into A14, then the reference will be to J14.
But if the cell is EXPLICITLY referenced (It is prefaced by a worksheet name) whether taht reference is on a different worksheer or even the SAME worksheet, then when sorted the reference is "locked" onto that cell. For example if a cell starts in A12 with a reference to Sheet1!J12 , if after the sort the formula goes into A14, the reference remains Sheet1!J12.
This can yield interesting results. If you have a formula in Sheet1 cell A12 which is:
=J12 + D12 + Sheet1!J12 + Sheet2!D12
Note that even though since the formula is IN Sheet1 that the IMPLICIT J12 and the EXPLICIT Sheet1!J12 refer to the SAME cell, when the formula is sorted, that "sameness" is ignored and if that formula ends up in A14 after the sort, the new formula will be:
=J14 + D14 + Sheet1!J12 + Sheet2!D12
The implicit references (without sheetnames) "copy" with the sort, but the explicit act like a "move" and stay linked to the starting reference.
Steve

20120131, 00:34 #9
 Join Date
 Jan 2001
 Location
 La Jolla, CA
 Posts
 1,222
 Thanks
 17
 Thanked 45 Times in 44 Posts
Hmm. Quite interesting. I suspect the only way I can resolve this is to rearrange the columns so that I can select a contiguous set over which to short (since CTRL selection won't work with sorting). If you have any other clever ideas, I'm all ears!!!
Kevin

20120131, 10:50 #10
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
You could move the formulas that have some explicit references completely outside the range to the right or even hiding them to the left "offscreen" and then putting a blank column between the other columns and it. You can use an implicit reference to reference that cell within the range for display purposes and this will sort correctly keeping a reference the formula in that row. The unsorted formulas will continue to stay referenced to the row they are on.
Steve

20120131, 11:55 #11
 Join Date
 Mar 2002
 Location
 Newcazzle, UK
 Posts
 2,218
 Thanks
 100
 Thanked 337 Times in 325 Posts
Hi
Steve has given the answer why EXPLICIT references behave differently from IMPLICIT references.
It has always been so, and still is in Excel2010. And it would be nuts if it were otherwise.
The clue is in the EXPLICIT. You are using a sheet reference to a cell. This would be on a different sheet to the one you are on.
Therefore, it should always remain 'associated' with the cell containing it during any sorting.
In a formula, you do not normally refer to and use the same sheetname you are currently on.
(Except in very very very rare cases)
Re: Specifics: here's the formula in I12.
=IF(ISNUMBER(F12),ROUND(F12*VLOOKUP('MASTER GRADE SHEET'!G12,'Course Codes'!$A$4:$D$1000,4,FALSE)/113,0),"NH")
If this formula is actually in cell [I12] on sheet [MASTER GRADE SHEET], then just change the formula to:
=IF(ISNUMBER(F12),ROUND(F12*VLOOKUP(G12,'Course Codes'!$A$4:$D$1000,4,FALSE)/113,0),"NH")
i.e. change the 'MASTER GRADE SHEET'!G12 to just G12 in the formula.
I suspect this is what you are actually doing.
When formulas are being entered, if you point to another sheet but then come back to the same sheet, Excel will preface all subsequent cell selections in formulas with the current selecetd sheet name.
zeddy

The Following User Says Thank You to zeddy For This Useful Post:
ruosChalet (20120204)

20120131, 15:08 #12
 Join Date
 Jan 2001
 Location
 La Jolla, CA
 Posts
 1,222
 Thanks
 17
 Thanked 45 Times in 44 Posts
Zeddy, you hit the nail on the head. The user, when inserting the formula, clicked on the sheet in which the formula was being placed (MASTER GRADE SHEET) and it should have been just the G12 ref. NICE going! Thanks.

20120202, 04:42 #13
 Join Date
 Dec 2011
 Posts
 2
 Thanks
 0
 Thanked 0 Times in 0 Posts
Interesting read, I've always spotted when this has happened for myself while entering a formula and corrected it. But it is worth knowing what would have been the outcome if left alone. Thanks zeddy and Kweaver.

20120202, 09:20 #14
 Join Date
 Mar 2002
 Location
 Newcazzle, UK
 Posts
 2,218
 Thanks
 100
 Thanked 337 Times in 325 Posts
It happens quite a lot when entering some formulas where you may need to point to another offsheet range, e.g with Vlookups, Match, Index, Offset etc. It is always worth 'correcting' any selfreferenced sheet names if only to save on storage using the shorter corrected formula, especially if you have to 'copy' the formula cell to many hundreds (or thousands..) of cells. And the formula is easier to read after correcting too.
zeddy

20120203, 14:24 #15
 Join Date
 Jul 2010
 Location
 Raleigh, NC
 Posts
 211
 Thanks
 11
 Thanked 20 Times in 17 Posts