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

[Bo_Ry]

Please try at C5 and copy across

=INDEX('Teacher Tracker'!$C$14:$F$183,C$4*8-7,MATCH($B5,'Teacher Tracker'!$C$4:$F$4,))

[heyryy]

Please try at C5 and copy across

=INDEX('Teacher Tracker'!$C$14:$F$183,C$4*8-7,MATCH($B5,'Teacher Tracker'!$C$4:$F$4,))

Amazing, it works. I must learn the INDEX and MATCH function!

[heyryy]

Please try at C5 and copy across

=INDEX('Teacher Tracker'!$C$14:$F$183,C$4*8-7,MATCH($B5,'Teacher Tracker'!$C$4:$F$4,))

One last thing, if I want to add a new Teacher (new column in "TT" and new row in "ST") to track, how can I copy these formulas?

I will soon record a macro to automatically add a new teacher.

Thanks!

[heyryy]

Ah no worries, I sorted it.

I simply set the range to last column XFD.

Thanks again!