Search through multiple worksheets and copy cells to another if a specific text matches

Hi,
Thank you all for your efforts to help solve my problem.
However, my boss had added a new twist to my earlier post.

I need help to populate students’ transcript from three worksheets. I want to select the student’s name from the validated list on the Transcript sheet so that the formula will look through the “JHS1 TERM 1”, “JHS1 TERM 2”.....“JHS3 TERM 3” sheets and copy the appropriate marks of selected student in the various subjects into their corresponding cells on the Transcript Summary.

I don’t know how, but I want to take into consideration if a student is transferred and another is admitted 'mid-stream' as in the case of the last student(s) in the workbook.
A workbook is attached.

Thanks in advance

In TRANSCRIPT

in D11

=INDEX('TERM 1'!$BX$2:$CH$100,MATCH($D$4,'TERM 1'!$C$2:$C$100,0),1)


Copy down and change highlighted reference to reflect column in range BX:CH

Repeat for other years change "term 1" to "Term 2"

NOTE: there is no check for form (JHS n) as I was not sure whether or not a student was unique to a form

EDIT: all formula should be ...

=IFERROR(INDEX('TERM 1'!$BX$2:$CH$100,MATCH($D$4,'TERM 1'!$C$2:$C$100,0),$A11),"")

I used column A as column reefrence numbers


and your Data Validation list needs to a complete student set (not Term 3 as now).

I changed lots of things here Blue shading). I also had to add an (incomplete??) table correlating short/long subject titles. Happy to explain it all, but not until you're satisfied that it is what you need.

Hi,
Thank you all for your efforts to help solve my problem.
However, my boss had added a new twist to my earlier post.

In the new request, a student will offer only one of these three subjects - Pre-Technical, Visual Arts and Home Econs (BDT) after JHS1
Therefore, an average of the three is needed for the JHS1 BDT
Again, the chosen subject from JHS2 should be the the BDT option on the Transcript summary.

A new workbook is attached.

Thanks in advance