MS Excel: Indirect Function

Excel's INDIRECT function accepts a text string as an argument,and then evaluates the text string to arrive at a cell or range reference.Assume that cells B2,C2,D2 on our Master worksheet hold the student's name.
The following formula utilizes the INDIRECT function to create the range reference used by the SUM function(Cell B2 to B6 carry individual marks for each subjecton different sheets named after students referred in B2 to D2):

=SUM(INDIRECT(B2&"!$B$2:$B$6"))

Note that the ampersand operator to join the student name with the cell reference (expressed as text).Refer the example below:The cell B2 contains the text Sam,the SUM function returns the sum of the range Sam!B1:B6.