I am trying to link data from one worksheet to another worksheet in the same
workbook.

The first worksheet (roster) includes student information, status (active or
inactive) and assessment units completed (designated as Y or N for each unit)
in columns B to G.

The subsequent worksheets include the data for each unit as well as the
student information--the values for student information are coming in from
the roster worksheet into columns A to F of the second worksheet (Unit 1).
The student's scores are in columns G to BC.

With the help of "Max" from a previous post--I used the following formula in
the second worksheet to return a value only if the student was active and had
completed the unit:

=IF(ROWS($1:1)COUNT($BJ$9:$BJ$148),"",INDEX(Roste r!B$9:B$148,SMALL($BJ$9:$BJ$148,ROWS($1:1))))

However, the formula returns a value for student information but the unit
assessment data is not linked with the student information. So when I sort
the worksheet or if a student goes from active to inactive--the data on that
row does not align with the correct student.

Would someone please be able to help me come up with a way to solve this
problem?