Within a formula, you aren’t limited to referencing only cells on the same worksheet. You can reference cells on other worksheets by using syntax like this:
=SUM(A5,Sheet2!C5)
This formula adds the value in cell A5 of the current worksheet to the value in cell C5 of Sheet2.
As when referencing cells on the same worksheet, you can enter a reference to a cell on another worksheet by clicking the sheet tab and then clicking the cell.
Tip
In the absence of a mathematical operator, the SUM() function adds the numeric values that have been entered in the formula and separated by commas. You can use a plus sign (+) in place of the commas if you want to, but it isn’t necessary. If you want to subtract numbers within a formula that uses the SUM function, insert minus signs (-) in place of the commas.
You can use multiple mathematical operators within an operation, adding some numbers and subtracting others, as shown in the next example. It is not necessary to use the SUM function for these simple mathematical operations.
Similarly, you can reference cells in other workbooks by using syntax like this:
=A5+Sheet2!C5-‘[Workbook2.xlsx]Sheet1’!D3
Tip
When referencing a workbook located in a folder other than the one your active workbook is in, enter the path to the file along with the file name. If the path includes a non-alphabetical character (such as the backslash in "C:") in the file name, enclose the path in single quotation marks.
The simplest way to reference a cell in another workbook is to enter the formula elements leading up to the reference, and then activate the workbook and click the cell or range of cells you want to reference. Excel will enter the cell or range reference into the formula.