I am working on a grades input spreadsheet.

Consider 3 sheets:

  1. A list of assessments containing references, names, weighing etc.
  2. A list of students, with IDs etc.
  3. A table with student names in rows referred from sheet 2; assessment names in columns transposed from sheet 1.

Now my problem is that once grades are entered in 3, if sheets 1 or 2 are sorted, the values in sheet 3 do not match with headers anymore.

How can I make sure that sorting values in sheet 1 or 2 will keep values in sheet 3 associated with their respective headers?

  • What type of forumula are you using to get data from Sheets 1 and 2 into Sheet 3? Commented Apr 3, 2014 at 11:27
  • Not using formulas. I've simply referenced the cells. Commented Apr 4, 2014 at 8:14

1 Answer 1


I've worked around my problem by keeping the sheets off reference and using a VLOOKUP() function instead. For instance I can look up a column for a name and find the corresponding grade.

=VLOOKUP("John", 'Other Sheet'!$E$4:$H$40, 2, FALSE)

The above sample would look up the range, trying to find the name "John" in the first column and retrieving the value from the second column (use FALSE if the first column is unsorted).

  • Good move - that's not a workaround, it's the simplest answer. Commented Apr 4, 2014 at 12:45
  • 1
    @MaryC.fromNZ Nice comment, it deserves an upvote then, doesn't (I just did)?
    – Jacob Jan
    Commented May 6, 2014 at 7:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.