Referencing A Sheet Indirectly
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:
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:
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.
Search for Tips
Browse Tips by Category
Needs tips? Here are two books, with nothing but tips:
Contains more than 200 useful tips and tricks for Excel 2007 | Other Excel 2007 books | Amazon link: John Walkenbach's Favorite Excel 2007 Tips & Tricks