Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    La Jolla,CA
    Posts
    1,108
    Thanks
    13
    Thanked 38 Times in 37 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.

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    6,506
    Thanks
    213
    Thanked 853 Times in 785 Posts
    K,

    Is the reference either a fixed reference, i.e. contains a $ or is it a Named Range {also a fixed reference}?
    May the Forces of good computing be with you!

    RG

    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs


  3. #3
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    La Jolla,CA
    Posts
    1,108
    Thanks
    13
    Thanked 38 Times in 37 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; 2012-01-27 at 19:33.

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 341 Times in 334 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

  5. #5
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    La Jolla,CA
    Posts
    1,108
    Thanks
    13
    Thanked 38 Times in 37 Posts
    That's really what happened. I copied the code, then did the sort and copied the resulting code. Very confused.

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 341 Times in 334 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

  7. #7
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    La Jolla,CA
    Posts
    1,108
    Thanks
    13
    Thanked 38 Times in 37 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 non-adjacent columns using CTRL doesn't work for the sorting option. GROANING.

  8. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 341 Times in 334 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

  9. #9
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    La Jolla,CA
    Posts
    1,108
    Thanks
    13
    Thanked 38 Times in 37 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

  10. #10
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 341 Times in 334 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

  11. #11
    Bronze Lounger
    Join Date
    Mar 2002
    Location
    Newcastle, UK
    Posts
    1,528
    Thanks
    32
    Thanked 180 Times in 174 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

  12. The Following User Says Thank You to zeddy For This Useful Post:

    ruosChalet (2012-02-04)

  13. #12
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    La Jolla,CA
    Posts
    1,108
    Thanks
    13
    Thanked 38 Times in 37 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.

  14. #13
    New Lounger
    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.

  15. #14
    Bronze Lounger
    Join Date
    Mar 2002
    Location
    Newcastle, UK
    Posts
    1,528
    Thanks
    32
    Thanked 180 Times in 174 Posts
    It happens quite a lot when entering some formulas where you may need to point to another off-sheet range, e.g with Vlookups, Match, Index, Offset etc. It is always worth 'correcting' any self-referenced 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

  16. #15
    3 Star Lounger
    Join Date
    Jul 2010
    Location
    Raleigh, NC
    Posts
    207
    Thanks
    10
    Thanked 19 Times in 16 Posts

    Arrow Use the Sort dialog?

    Quote Originally Posted by kweaver View Post
    I'd like to be able to bypass this one column in the sort, but selecting the first several columns and then selecting other non-adjacent columns using CTRL doesn't work for the sorting option. GROANING.
    It wouldn't solve the mystery, but perhaps it would your problem: Have you tried doing the sort using the dialog, where you get to specify the columns for the sort, rather than being stuck with only contiguous columns?

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •