+ Post New Thread
Results 1 to 10 of 10
Office Software Thread, Excel 2010 - Can't sort correctly with VLOOKUP in Technical; I've been working on a huge spreadsheet in Excel to help our Principal work out the setting and banding for ...
  1. #1

    Join Date
    Nov 2010
    Location
    Great Grimsby
    Posts
    72
    Thank Post
    7
    Thanked 2 Times in 2 Posts
    Rep Power
    8

    Question Excel 2010 - Can't sort correctly with VLOOKUP

    I've been working on a huge spreadsheet in Excel to help our Principal work out the setting and banding for Year 7.

    It's a series of marksheets and timetable and other data which I've exported from SIMS .net, and pasted into a number of sheets on an Excel workbook (having converted all the text to number, natch).

    Because some of the data was live, because SIMS .net doesn't populate assessment marksheets with our timetable and because I needed to present the front sheet in a specific order, I didn't want to create a single marksheet within SIMS to pull off the rest of the data, so the front page, which I've presented to the Principal, is basically a series of lookups from each of these other sheets with the odd column running calculations for good measure- eg FSM, SEN, Band, Maths Class, KS2 Result, Exam Result, Difference between KS2 and Exam, English Class...

    Each of the "marksheets" has been exported unformatted and in alphabetical order of surname. I've ensured that the name is formatted identically on each sheet.

    So far, so good, and when the front page is sorted alphabetically by the first column - name, everything is Hunky Dory, the results are next to the correct names and everything.

    However, if I try to sort by any other column (ie changing the order of names on the front sheet from those on the Lookup arrays), then the results in all the subsequent columns do not sort correctly.

    I've worked around the problem by copying the sheet with the lookups and pasting to another blank sheet values only, but I'm hoping that I can make the system a bit more automated and avoid having to do this.

    Does anyone know what I have to do to make these values sort correctly?

  2. #2

    elsiegee40's Avatar
    Join Date
    Jan 2007
    Location
    Kent
    Posts
    11,185
    Thank Post
    1,803
    Thanked 2,215 Times in 1,633 Posts
    Rep Power
    802
    I've no experience of your version of Excel, but in Excel 2003 you must select all the data in every row to be sorted before actually doing the sort. Otherwise, the result is as you describe.

  3. #3

    Join Date
    Nov 2010
    Location
    Great Grimsby
    Posts
    72
    Thank Post
    7
    Thanked 2 Times in 2 Posts
    Rep Power
    8
    Aha! Turns out that it wasn't the VLOOKUP that was completely at fault. More my hamfisted construction of the arguments.

    Only a few columns were faulty, so I thought I'd delve a bit deeper.

    Turns out that I'd put the name of the worksheet in front of the cell reference of the lookup value, when it was looking for a value on the same cell. Consequently this was seen as invariable and didn't update automagically as the rest of the sheet did when applying the filters.

    I took the worksheet name off and Robert is now my mother's brother!

  4. #4

    Join Date
    Nov 2010
    Location
    Great Grimsby
    Posts
    72
    Thank Post
    7
    Thanked 2 Times in 2 Posts
    Rep Power
    8
    OK, this has come back to bite me on the bum.

    The column that I want to sort contains Finely Differentiated scores calculated using a simple formula bassed on the results of the previous two columns.

    If I select the whole sheet, then sort on the column I wish to sort, it sorts the column into a random order. If I then undo and sort again, it sorts the column into a totally different order.

    The results are correct if I look along the row, but I cannot for the life of me get it to sort the set of results in order according to this one specific column.

    Again, copy the results and paste the values into a new sheet - worksheet works fine.

    I cannot google anywhere anyone who's had anything similar.

  5. #5

    Join Date
    Nov 2010
    Location
    Great Grimsby
    Posts
    72
    Thank Post
    7
    Thanked 2 Times in 2 Posts
    Rep Power
    8
    We've had a couple of us look at it and replicated the error elsewhere.

    Column 1 is Names, Column 2 is numbers based on a lookup, matching the lookup value to the name in column 1.
    Column 3 is numbers based on a different lookup table, matching the lookup value to the name in column 1.
    Column 4 Adds the two columns together, multiplies by 6 and divides by 2.

    Sort on Column 1 - OK
    Sort on Column 2 - OK
    Sort on Column 3 - OK
    Sort on Column 4 - randomness.

    Copy the values of Column 4 and paste elsewhere - sorts fine.

    Anyone got a work-around that doesn't require copy/pasting, so I can enter data in one place and have everything update?

    Thing is, I can think lof many occasions when this would be useful...

  6. #6

    CESIL's Avatar
    Join Date
    Nov 2006
    Location
    Hampshire
    Posts
    1,404
    Thank Post
    109
    Thanked 267 Times in 198 Posts
    Rep Power
    168
    I have tried to replicate this in Excel 2003 and my columns seem to sort ok...can you post a sample sheet so I can try that?

  7. #7

    Join Date
    Oct 2010
    Posts
    23
    Thank Post
    6
    Thanked 1 Time in 1 Post
    Blog Entries
    2
    Rep Power
    42
    This is a bit of a stab in the dark, but there are two things that might be worth checking in your data if everything works ok when you copy values to a second worksheet:

    • If the fourth column is a calculation on the other columns, check your use of relative and absolute cell references in the formulas.
    • If the formula in the fourth column uses named ranges (e.g. the other cells in that row are named and the formula is based on that) then when you sort the formula will refer to the wrong cells. (In other words, the names won't move with the sort.)


    Apologies if you've already looked at those possibilities.

    HTH.

  8. #8

    Join Date
    Nov 2011
    Location
    Australia
    Posts
    2
    Thank Post
    0
    Thanked 0 Times in 0 Posts
    Rep Power
    0
    I'm having the same problem, and I've checked my relative / absolute cell references and I'm not using named ranges. Don't suppose anyone's found a solution?

  9. #9

    Join Date
    Nov 2011
    Location
    Australia
    Posts
    2
    Thank Post
    0
    Thanked 0 Times in 0 Posts
    Rep Power
    0

    Talking Found the solution! :-)

    Aha! Found the solution! The problem was that my VLOOOKUP was referring to its own worksheet by the worksheet name, and when I took that out, it was fine.

    An example might help:
    On worksheet DATA, I had this formula for a cell:
    =VLOOKUP(DATA!A22,CODES!$A$2:$G$664,1,FALSE)

    Note that this is in worksheet DATA, and it's using cell A22 in that same DATA worksheet to look up a value from another worksheet (CODES). This caused problems when I tried to sort on the VLOOKUP column.

    But you don't need to say DATA!A22 because we're on the DATA worksheet. You can just say A22, and this means everything works! Just change the formula to:
    =VLOOKUP(A22,CODES!$A$2:$G$664,1,FALSE)

    ...and we're laughing! Woo hoo!

    Hope this works for you too.


    Quote Originally Posted by tigger View Post
    I'm having the same problem, and I've checked my relative / absolute cell references and I'm not using named ranges. Don't suppose anyone's found a solution?

  10. #10

    Join Date
    Dec 2011
    Posts
    1
    Thank Post
    0
    Thanked 0 Times in 0 Posts
    Rep Power
    0

    I have a similar problem

    Hi I have started to have a similar problem but I am not using vlookup and I was able to sort the sheet previously but it seems once I had added a couple of extra lines of data it would no longer sort properly it drops values and does not put the remaining ones in order. I only want to sort one column and I dont want the rest of the colmns to move. It has worked fine for nearly four years in all different versions of excel until about a month ago cant remember if there has been any updates to excel in that time there was one in september specifically for excel but thats all cant say if the problem coincided with then as I dont perform this function every week.

SHARE:
+ Post New Thread

Similar Threads

  1. [MS Office - 2010] Excel 2010 Subtotal/Autosum
    By Reaper in forum Office Software
    Replies: 0
    Last Post: 1st February 2011, 09:46 AM
  2. [MS Office - 2010] Excel 2010 Sorting data
    By ful56_uk in forum Office Software
    Replies: 1
    Last Post: 31st December 2010, 12:04 PM
  3. Replies: 0
    Last Post: 25th November 2010, 12:25 PM
  4. Windows 7, Excel 2010 and Printers
    By Homer in forum Windows 7
    Replies: 6
    Last Post: 9th September 2010, 12:31 PM
  5. [MS Office - 2003] vLookup
    By Tricky_Dicky in forum Office Software
    Replies: 5
    Last Post: 16th November 2009, 04:58 PM

Thread Information

Users Browsing this Thread

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

Tags for this Thread

Posting Permissions

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