How to Reference Another Sheet in the Same Excel File
A basic cell reference is written as the column letter followed by the row number.
So the cell reference B3 refers to the cell at the intersection of column B and row 3.
When referring to cells on other sheets, this cell reference is preceded with the other sheet’s name. For example, below is a reference to cell B3 on a sheet name “January.”
=January!B3
The exclamation point (!) separates the sheet name from the cell address.
If the sheet name contains spaces, then you must enclose the name with single quotation marks in the reference.
='January Sales'!B3
To create these references, you can type them directly into the cell. However, it is easier and more reliable to let Excel write the reference for you.
Type an equal sign (=) into a cell, click on the Sheet tab, and then click the cell that you want to cross-reference.
As you do this, Excel writes the reference for you in the Formula Bar.
Press Enter to complete the formula.
How to Reference Another Excel File
You can refer to cells of another workbook using the same method. Just be sure that you have the other Excel file open before you begin typing the formula.
Type an equal sign (=), switch to the other file, and then click the cell in that file you want to reference. Press Enter when you’re done.
The completed cross-reference contains the other workbook name enclosed in square brackets, followed by the sheet name and cell number.
=[Chicago.xlsx]January!B3
If the file or sheet name contains spaces, then you’ll need to enclose the file reference (including the square brackets) in single quotation marks.
='[New York.xlsx]January'!B3
In this example, you can see dollar signs ($) amongst the cell address. This is an absolute cell reference (Find out more about absolute cell references).
When referencing cells and ranges on different Excel files, the references are made absolute by default. You can change this to a relative reference if required.
If you look at the formula when the referenced workbook is closed, it will contain the entire path to that file.
Although creating references to other workbooks is straightforward, they are more susceptible to issues. Users creating or renaming folders and moving files can break these references and cause errors.
Keeping data in one workbook, if possible, is more reliable.
No comments:
Post a Comment