Monday, June 24, 2013

How to Calculate Excel Formulas With Multiple Pages


3-D Reference: Contiguous Worksheets
1. Create an Excel workbook with four worksheets.
2. Enter a numeric value in cell A1 of Sheet2, Sheet3 and Sheet4. Start by making a simple formula to sum up values across these three worksheets.
3. In any cell on Sheet1 enter an equals sign followed by your function and an opening parenthesis. For our example, enter “=SUM(”.
4. Click on the 'Sheet2' tab at the bottom of your workbook and select the cell in Sheet2 that you want to include in your sum. For our example, use cell A1.
5. Hold down the shift key and click on the 'Sheet4' tab. The formula updates to include the same cell on Sheet4 that you selected for Sheet2. Complete the formula by entering a closing parenthesis. Your formula will look like this: =SUM(Sheet2:Sheet4!A1). This formula sums up the values in cell A1 on Sheet2, Sheet3 and Sheet4. Notice the format of the resulting formula. The colon between the sheet names indicates that the formula spans all sheets from Sheet2 through Sheet4.
3-D Reference: Selected Worksheets
6. In another cell on Sheet1 enter “=SUM(”. For this example, sum up cell A1 on Sheet2 with cell A1 on Sheet4. This time rather than clicking on cells to select them for the formula, enter the worksheet and cell references directly.
7. Enter the first argument for your sum. The first argument is “Sheet2!A1”. In this case, “Sheet2!” is the worksheet reference and “A1” is the cell reference.
8. Enter a comma followed by the second argument. The second argument is “Sheet4!A1”, where “Sheet4!” is the worksheet reference and “A1” is the cell reference.
9. Enter a closing parenthesis. Your complete formula should look like this: =SUM(Sheet2!A1,Sheet4!A1). This formula will sum up the A1 cells on Sheet2 and Sheet4 and exclude the value in cell A1 on Sheet3. Notice how the format of this formula differs from the previous formula. The references are separated by a comma, indicating that only the stated references are included in the calculation.

Blogger news