Finding a person's specific Data with a formula

I have a report sheet that is attached to multiple sheets.

I am manually taking the data out for each week for each person.

Is there a formula for it to automatically find the person's name and check what data is needed for that cell then go to the right sheet, find that person and find the specific data?

Hi Kramm222
I expect a formula or macro could be written, but we'd need more information, eg:

• where should the formula look for the person's name (sheet name and range),
• where is the data to be copied (e.g. column to the right of the name)
• where is the data to be copied to

Hi
this macro might work
to explain:

it finds the last block of characters in the title cell (ie cell A53), which is the week number
for each cell in the range "teachers" (the range in shee Utility with the names in), it:

• finds the column with that name in row 2 of the sheet with the name name as the week number
• finds the value row in 361 of the column to the right of that column
• copies this value into the cell 3 columns to the right of the teacher's name in sheet "utility"

Sub HoursFind()
Dim Teachers As Range, TTell As Range, TName As String, WeekNum As Variant, arrNos As Variant, NameCol As Long, SearchRange As Range

'find week number
arrNos = Split(Sheets("Utility").Range("A53").Value, " ")
WeekNum = Trim(arrNos(UBound(arrNos)))
Set SearchRange = Sheets(WeekNum).Rows(2)

Set Teachers = Sheets("Utility").Range("C55:c63")

For Each Tcell In Teachers.Cells
    TName = Tcell.Value
    NameCol = Application.WorksheetFunction.Match(TName, SearchRange, 0)
    Tcell.Offset(0, 3).Value = Sheets(WeekNum).Rows(361).Columns(NameCol).Offset(0, 1).Value
Next Tcell
End Sub

Thanks!

This works perfectly!

The only 3 issues are
1) I'm not familiar enough with macro's yet to be able to understand the coding
2)because of reason 1 I dont know how to adjust this to make it work for the other columns
3) I was hoping more on finding out how to use a formula/function instead of a macro

For anyone reading this:
This Macro DOES WORK!!!
It's just not what i was looking for.

Originally Posted by Haseeb A

Try this in F55

=VLOOKUP("Total Hrs",INDIRECT("'"&RIGHT(A$53,4)&"'!B:ALL"),MATCH(C55,INDIRECT("'"&RIGHT(A$53,4)&"'!B2:ALL2"),0)+1,0)

then copy down.

This is what i was looking for.
only issue is that I dont get it.
could anyone explain the layout of this formula?