Trouble sorting linked worksheet - need help

[paulfj]

How can I sort a worksheet and have data on a linked worksheet update?
Here's my situation:

I have 1 worksheet (Attendance) that keeps track of attendance. Rows
11 - 37 (27 students) with columns J - DQ for recording daily
attendance.

I'm designing a 2nd worksheet (Progress) in the same workbook to keep
track of progress.

Currently, the student names on Progress are linked to those on
Attendance. This works fine. However, when I sort Attendance, only
the student names on Progress move causing information on this second
sheet to no longer correspond to the correct student.

Is there a way to sort Attendance and have the data I enter on Progress
stay with the names they're supposed to?

Thanks for your help.

 

[vezerid]

From what I can tell, the data in Progress are not related to
Attendance, i.e., save for the student id, the rest is information
calculated otherwise. Now, you would like the Progress table be sorted
so that it matches the attendance.

I suggest that you manage Progress separately, i.e. do not link the
student id to Attendance. Then you can have a third worksheet,
something like ProgressByAttendance, which reproduces the information
in Progress, in which you do the following:

Student ID is linked to Attendance as you have it now. It will be
sorted when Attendance is sorted.
All other information in ProgressByAttendance is taken using VLOOKUP()
from the Progress table:
=VLOOKUP($A2, Progress!$A:$K,Column(), 0)

If in the ProgressByAttendance sheet your headers start from A1 and
data from A2, then the above formula can be copied down and to the
right as far as necessary.

HTH
Kostis Vezerides

 

[paulfj]

Thanks for your input. I was hoping for a process that minimized data
entry since this will be used by over 100 instructors who, while great
teachers, aren't terribly fond of paperwork. As such, they tend to be
prone to errors that we spend a lot of time finding and resolving.

Nonetheless, your advice is very helpful. Thanks!