I've done a little searching and can't find a similar question but sorry if this duplicates!

I have 3 tables in different sheets. 2 have original information about students (I know it would be easier just to put it all in the same table but there is good reason not to!)

The third table is a summary that brings in the names and selected information from the other 2 using a straightforward =[cell on other sheet with info].

The problem with this is that if I sort the any of the tables all the info gets mixed up. If there is a solution to this I would be very keen to hear it. The only 2 I have thought of are:
  1. vlookup to pull in the values. The problem with this is that the values may not be in ascending order. (I'm new to vlookup but I think this is a requirement).
  2. Some way of automatically updating the reference so when the name/info in B5 moves with the sort the dependent cell updates the reference. This seems logical but I don't know if it is possible and I certainly don't know how to do it.

It may be that this is not possible in which case a quick 'can't do it' from someone who knows more than I do would save me a lot of time trying to work it out!

Thanks for your help

Matt