Find value and copy formula to other cells

[heyryy]

Hey all,

I'm working on a spreadsheet. I have two sheets 'Student Tracker' (ST) and 'Teacher Tracker' (TT). On the ST sheet, I'd like the cells in a table to find values from a table in TT.

------------------------------------------------------------------------------------------------------

Here's an example: *view the sample"

Find the number of students 'Becky' has throughout the term, week-by-week. This information is on the TT on each row named 'Total'. 1st week should be 40, 2nd 43, 3rd 44, and so on.

------------------------------------------------------------------------------------------------------

The most important thing is to use the fill handle to copy the formulas to the rest of the teachers. This is what I can't accomplish. It's possible I'm making a small mistake somewhere! I've tried VLOOKUP, OFFSET, and some others, with no luck. When using the fill handle, the cell after the fill handle displays the value of the first cell.

This is important because later I will use macros to add and remove teachers.

Many thanks,
Ryan

Real English Student Teacher Tracker (Gaoming Branch) 1.xlsm