When you want to create a formula which uses data from several worksheets, you create a 3-D Formula. A 3-D Formula is created using 3-D References, that is to say, references to cells in a different worksheet. To create a 3-D Reference, the format is:
‘Sheet Name’!Cell Name
For example, suppose we have three sheets named 2000, 2001 and Yearly Totals. In the Yearly Totals sheet, we want to calculate the sum of the values in cell C18 from both the 2000 and 2001 sheets. Thus, our formula in the Yearly Totals worksheet would be:
=‘2001’!C18 + ‘2000’!C18
To use a function such as SUM, AVERAGE, etc. in our 3-D formula, the format is:
=SUM(‘Sheet1:Sheet2’!C12:C35)
You can create your 3-D formula yourself by typing it into the cell or you can manually select the appropriate worksheets and cells to include in your formula.
To Create a 3-D Formula
- In the cell where you want your formula to display, type = and the beginning of the formula, such as: =Sum(
- Group the worksheets whose data you will include in the formula if the data is in the same cell address in each worksheet.
- Select the cell or cell range to include in the formula.
- Type the remainder of the formula if applicable and press Enter.
Similar Posts:
- Grouping Worksheets in Excel
- Change Worksheet Tab Color in Excel
- Easily Move or Copy a Worksheet in Excel
- Using Relative References in Excel
- Using the VLOOKUP Function in Excel
- Apply Cell Styles in Excel 2007
- Locking and Unlocking Cells in an Excel Worksheet
