Excel trick: summarising data in multiple worksheets

If you have a workbook containing many similar sheets, it may be useful to have a summary table of key values. Take Figure 1 as an example, and suppose it is one of a number of monthly reports with identical layouts. How can we tabulate certain values from every similar worksheet, so that there is a column for the monthly quantity of each fruit?

Figure 2 is a worksheet designed to provide just such a summary. It would reside in the same workbook as the "source" sheets. In column A it has the names of the those worksheets (1), while in row 1 there is a list of the cell-addresses of the required data items (2). The summary table is a grid of cells each containing formulae such as:

=INDIRECT("'"&$A4&"'!"&B$1)

(to take the case of cell B4). Excel's INDIRECT(Ref) function returns the value of the cell whose address is given by Ref. So for example if cell C3 contained "T9", then =INDIRECT(C3) would return the value in cell T9. Changing the contents of C3 to "'Nov04'!C5" would make the INDIRECT() function return a value from cell C5 of sheet Nov04.

This is what formula in our table does, except that it uses string arithmetic to compose the indirect address based on the sheet-name at the left hand end of the row and the cell address at the top of the column.

Figure 1: one of a number of similar worksheets

Figure 2: the summary sheet