Referencing A Sheet Indirectly

Category: Formulas | [Item URL]

Q. My Excel workbook has a sheet for each month, named January, February, and so on. I also have a summary sheet that displays key calculations for a particular month. For example, one of my formulas is:

=SUM(February!F1:F10) 

Is there any way that I can enter the month name into a cell on my summary sheet, and then have my formulas use the data for the specified sheet?

Yes. Excel's INDIRECT function was designed specifically for this sort of thing. This function accepts a text string as an argument, and then evaluates the text string to arrive at a cell or range reference. In your case, assume that cell B1 on your summary worksheet holds the month name. The following formula utilizes the INDIRECT function to create the range reference used by the SUM function:

=SUM(INDIRECT(B1&"!F1:F10"))

Note that I use the ampersand operator to join the month name with the cell reference (expressed as text). Refer to the figure below. If cell B1 contains the text March, the SUM function returns the sum of the range March!F1:F10.