Copyright © 2010 Excel Free.com. All Rights Reserved. Snowblind by Themes by bavotasan.com. Powered by WordPress.
I have eight years of data in eight different Microsoft Excel tables in one spreadsheet. I want to add the cell X2 from each table together and place it on a ninth table. Is it possible to do this?
February 10, 2010 at 7:52 am
Do you mean “eight years of data in eight different Microsoft Excel worksheets in one workbook” as multiple tables on one spreadsheet would have different cell references.
If you do have multiple worksheets then the formula in cell X2 in the 9th worksheet would be
=SUM(Sheet1!X2 + Sheet2!X2 + Sheet3!X2+ Sheet4!X2+ Sheet5!X2+ Sheet6!X2+ Sheet7!X2+ Sheet8!X2 )
The reference to Sheet1 to Sheet8 would be changed to the names of the sheets in your workbook (if you have spaces in the name put the name in square brackets like [Sheet 1]
February 10, 2010 at 8:05 am
First of all, it would have been helpful to know what version and update level of MS-Excel you are using, so as to give specific details for it.
Secondly, your question and details are not all that clear, leaving us to assume what you mean by “table”. Is that a single Row of values, or a separate tab. An MS-Excel file is called a Workbook, each tab located along the lower left-hand portion is called a Worksheet, all of the various grid boxes are known as Cells, and they are made from Rows of numbers and Columns of letters.
If you meant eight Rows of data, with each one being a different year, then just use the AutoSum button in a blank cell for column “X”.
If you meant eight Worksheets of data, then use a Reference to those Worksheets and Cells:
= SUM( Sheet1!X2, Sheet2!X2, Sheet3!X2, Sheet4!X2, Sheet5!X2, Sheet6!X2, Sheet7!X2, Sheet8!X2 )
There are two styles of referencing in MS-Excel, and by default, Excel uses the A1 reference style, which refers to columns with letters and rows with numbers. For example:
. . . . . . . . . . To refer to . . . . . . . . . . . . . . . . . . . . . . Use . . .
.
The cell in column A and row 10 . . . . . . . . . . . . . . . . . A10
A range of cells in column A from rows 10 thru 20 . . . A10:A20
A range of cells in row 15 from columns B thru E . . . . B15:E15
All cells in row 5 . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5:5
All cells in rows 5 through 10 . . . . . . . . . . . . . . . . . . . 5:10
All cells in column H . . . . . . . . . . . . . . . . . . . . . . . . . H:H
All cells in columns H through J . . . . . . . . . . . . . . . . . H:J
A range of cells in columns A-E and rows 10-20 . . . . A10:E20
A “Relative” cell reference in a formula, such as A1, is based on the relative position of the cell that contains the formula and the cell the reference refers to. If a cell contains a formula, and the position of the cell changes, then the reference is changed. If you copy the formula across rows or down columns, the reference automatically adjusts. By default, new formulas use relative references. For example, if you copy a relative reference in cell B2 to cell B3, it automatically adjusts from =A1 to =A2.
An “Absolute” cell reference in a formula, such as $A$1, always refer to a cell in a specific location. If a cell contains a formula, and the position of that cell changes, then the absolute reference remains the same. If you copy the formula across rows or down columns, the absolute reference does not adjust. By default, new formulas use relative references, and you will need to switch them to absolute references if you wish to always refer to the same location no matter where that formula gets moved to. For example, if you copy an absolute reference in cell B2 to cell B3, it stays the same in both cells =$A$1.
A “Mixed” reference has either an absolute column and relative row, or absolute row and relative column. An absolute column reference takes the form $A1, $B1, and so on. An absolute row reference takes the form A$1, B$1, and so on. If the position of the cell that contains the formula changes, the relative reference is changed, and the absolute reference does not change. If you copy the formula across rows or down columns, the relative reference automatically adjusts, and the absolute reference does not adjust. For example, if you copy a mixed reference from cell B2 to C3, it adjusts from =A$1 to =B$1.
Switching between Absolute and Relative:
1. Select the cell that contains the formula.
2. In the Formula Bar, select the reference you want to change.
3. Press F4 to toggle through the combinations.
.