Tip Printed from ExcelTip.com
Consolidate with formulas if the row & column headers of all worksheets are identical in Microsoft Excel


If you have several worksheets and every worksheet has an identical structure - that is, the headings in row 1 and column A are identical from sheet to sheet, you can create a total worksheet using simple formulas.

Example:
In the workbook 3 sheets - January, February and March contain tables with salary data.
On every sheet, the items in cells A2:A8 are identical.
On every sheet, the employees listed in B1:H1 are identical and never change.
Add a new worksheet, change the worksheet name to Total. Use a formula like the one shown in cell B2 ( see screen shot ) to add the other sheets.
The formula in Cell B2 is =SUM (January:March!B2).

The technique for entering a formula to sum a single cell on a number of sheets.
  1. Select Cell B2 in the sheet name Total.
  2. In the cell, type =SUM, and press Ctrl+A (the shortcut for displaying the formula argument box).
  3. In the first argument box, select the name of the first sheet in the range January.
  4. Press Shift, and select the last sheet in the range March.
  5. Click OK.