Generating Report Card

[GBENGA]

Thanks for your good works.
I have a workbook that calculates students scores in an academic session. Each worksheet represent each subject. The report sheet is designed to pull the scores of each student from the different work sheet to produce a report card for the pupil.
My question:
From a drop down tab on the report sheet worksheet, how do i pull all the students score to the table below by selecting the name on the drop down list? Also, how do i create report for each term?
workbook attached.
Thank you

 

[Raptor_5]

Hi GENGA, you may be looking to use the =Vlookup function, using a drop down in the student name cell. Then use it again too populate their marks ref to their name in the dropdown.

 

[Peter Bartholomew]

It depends on the extent to which you wish to 'hand craft' each formula or whether it is worth the effort to create a formula that will populate many cells on the report sheet. The minimum is to be able to look up the sequence number for the selected student. Using MS365 this might be
= XLOOKUP( StudentName, Tbl_Student_Names, SubjectMarks)
but INDEX/MATCH or VLOOKUP can also work.
If you want the formula to pick results from different sheets, then you need to read the subject and then use either INDIRECT or CHOOSE to return the range 'Marks' from the appropriate sheet; e.g. 'SubjectMarks' might be defined to refer to
= CHOOSE( SubjectIndex, NUMM, QUANT, ... )
One device I use is to use sheet local names so that each subject would have a range 'Marks' referred to off-sheet as 'NUMERACY!Marks', etc.
To move from term to term may require a further index to offset the range to be returned.