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?