Index formula-change cell range

[Wilma]

I am trying to link the data from several worksheets. One worksheet (roster)
contains student information (student name, ID, status (A or I) etc.) and
units completed. The other worksheets contains the unit assessment data for
each unit.

The following formula is giving me the values for student
information--however, the unit assessment data is not linked with the student
information.

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

Can someone please show me how to change the range so that all of the data
in a row is linked to the student appearing on that row.

Thanks!

 

[Luke M]

Copy the formula to the right to get the information from the other columns.
This will cause this part (Roster!B$9:B$148) to change.

 

[Wilma]

The range I am trying to change is not in the roster worksheet--it is in the
second worksheet (unit data).

Do I need to change the formula to add the range from the second worksheet
and if so --how do I do it?