+ Reply to Thread
Results 1 to 8 of 8

VLOOKUP no longer sorting groups? What changed?

  1. #1
    Registered User
    Join Date
    02-10-2016
    Location
    USA
    MS-Off Ver
    2013
    Posts
    65

    VLOOKUP no longer sorting groups? What changed?

    Hi All,

    I have attached a workbook here, but this post is only interested in Worksheets 1 ("Tracker") and 2 "("Pipeline).

    Basically, "Pipeline" pulls ID#s into one of the 4 colored boxes by their group status ("Pending" for the first group, "Consented" for the next group, etc).

    You can see what that looks like in Pipeline! here:
    16-282 Study Tracker and Accrual 2016.08.17.xlsm

    However, something happened (literally no idea) while I was working, and next thing I know - Pipeline! is drawing blanks:
    16-282 Study Tracker and Accrual 2016.08.23.xlsm

    Can anyone help me figure out why the function stopped working? I don't believe I've changed any reference rows/columns.

    Alternately, I could ask if someone could just help me figure out a new formula that would work.

    Thank you in advance to the many talented people on here.

    -AW

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    It changes constantly, but based in Ireland.
    MS-Off Ver
    Excel 2013 & 2016
    Posts
    25,319

    Re: VLOOKUP no longer sorting groups? What changed?

    There's a whole pile of stuff here. WHERE EXACTLY are the formulae that are causing you problems?
    Glenn



  3. #3
    Registered User
    Join Date
    02-10-2016
    Location
    USA
    MS-Off Ver
    2013
    Posts
    65

    Re: VLOOKUP no longer sorting groups? What changed?

    Hi Glenn,

    The formulae are the ones located in the Pipeline! worksheet, cells A3, B3, C3.

  4. #4
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    It changes constantly, but based in Ireland.
    MS-Off Ver
    Excel 2013 & 2016
    Posts
    25,319

    Re: VLOOKUP no longer sorting groups? What changed?

    Take a look at the ranges in A3. They are not the same. Two stop at 102 and ROW stops at 103. Fix that and your formula works again. Maybe you should consider using dynamic named ranges instead of fixed ranges. It'll prevent that sort of problem from happening again.

  5. #5
    Registered User
    Join Date
    02-10-2016
    Location
    USA
    MS-Off Ver
    2013
    Posts
    65

    Re: VLOOKUP no longer sorting groups? What changed?

    Hi Glenn,

    Good note about dynamic ranged vs fixed ones - I know that I had problems with that angle previously so I stuck with this when it worked.

    I went back to the ranges you mentioned, and I have corrected it as such (copied from cell A3 from Pipeline!):

    =IFERROR(INDEX(Tracker!$A$3:$A$103,SMALL(IF(Tracker!$F$3:$F$103=Pipeline!A$1,ROW($A$3:$A$103)-ROW($A$3)+1,""),ROWS($A$3:A3))),"")

    I'm still drawing blanks. The key is that A3 is not pulling the code from Tracker!

    Thank you sir

  6. #6
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    It changes constantly, but based in Ireland.
    MS-Off Ver
    Excel 2013 & 2016
    Posts
    25,319

    Re: VLOOKUP no longer sorting groups? What changed?

    It works OK here. See attached file. Did you remember to enter it as an array formula?

    Array Formulae are a little different from ordinary formulas in that they MUST be confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER.

    You will know the array is active when you see curly braces { } appear around the outside of your formula. If you do not CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

    Don't type the curly braces yourself - it won't work...
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    02-10-2016
    Location
    USA
    MS-Off Ver
    2013
    Posts
    65

    Re: VLOOKUP no longer sorting groups? What changed?

    Totally nailed it. CTRL + SHIFT + ENTER it is.

    Thank you so much for catching that. I had completely forgotten about the array function entry.

    Glenn +10

  8. #8
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    It changes constantly, but based in Ireland.
    MS-Off Ver
    Excel 2013 & 2016
    Posts
    25,319

    Re: VLOOKUP no longer sorting groups? What changed?

    Glad to have helped.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 1
    Last Post: 12-16-2015, 01:04 PM
  2. Replies: 4
    Last Post: 07-22-2015, 07:13 AM
  3. Network domain changed, Macros no longer working
    By mrome74 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-31-2013, 10:34 PM
  4. Pivot table no longer sorting correctly (2003)
    By mlk in forum Excel General
    Replies: 0
    Last Post: 02-28-2011, 02:15 PM
  5. Replies: 0
    Last Post: 06-21-2007, 05:46 AM
  6. [SOLVED] Sorting with groups
    By ecounts in forum Excel General
    Replies: 0
    Last Post: 07-19-2006, 02:10 PM
  7. Sorting by Groups
    By grendel in forum Excel General
    Replies: 0
    Last Post: 03-26-2006, 07:15 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1