 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: =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? Roger Blattner A: 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 utilises 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). For example, FIGURE 2 illustrates how, if cell B1 contains the text March, the SUM function returns the sum of the range March!F1:F10. |
Category:Spreadsheets Issue: April 2000 |
These Web pages are produced by Australian PC World © 2000 IDG Communications