Creating a link to another cell mirrors the source's value in the linked cell, even if the source subsequently changes. You can manually code absolute or relative references to source cells that reside on the same sheet, different sheets or even different workbooks. However, if you prefer the mouse to the keyboard, you can use a mouse selection or the Paste Link function to add the reference for you.
Understanding Absolute Vs. Relative References
If you copy a formula containing a relative reference to another location, including a simple "=A1" linking formula, the formula changes to reflect the new location. As an example, if you copied "=A1" to the next cell down, it becomes "=A2." That's good if you want to create multiple links to different cells, but if you want to maintain the same reference, you need to use an absolute reference. By adding a dollar sign in front of the column letter and row number, such as "=$A$1," you make it an unchanging absolute reference. You can also highlight a reference within a formula and press "F4" to cycle between relative and absolute references, or a combination thereof.
Video of the Day
Directly Typing References
If you prefer typing to clicking, you can directly enter references, assuming you know the exact location of the source cell. To reference a cell on the same worksheet, simply type "=A1" (without quotes here and throughout) to establish a link, but change "A1" to reference the cell containing the source value. If the source cell resides on a different sheet, use the format "=SheetName!A1." You can also link a different Excel workbook using the format "=[FileName.xlsx]SheetName!A1."
Referencing With Your Mouse
It's often better to select the source file with your mouse, because it saves you the trouble of typing and ensures you use the correct reference and format. Simply type an equal sign in an empty cell, move to the other workbook or sheet and then click the source cell to automatically enter the reference. When you press "Enter," the link is established to the selected cell. If you're linking a cell in another workbook, save both workbooks first, so Excel uses the correct filename.
Using Paste Link to Establish Links
Rather than beginning the reference and switching to the source cell, you can effectively work the other way around. Highlight the source cell and then press "Ctrl-C" to copy it. Select the cell where you want the link established, click the "Paste" drop-down arrow from the Home tab's Clipboard group and then select the "Paste Link" icon. You can also press "N" when the Paste drop-down menu is activated to paste the link.
The first time you open a workbook that links to another workbook, you may receive a security warning. Click "Enable" to reactivate the links. If the source workbook is password-protected, you'll be prompted for the password. If you moved or renamed the source workbook after establishing the link, the link will no longer work, but Excel warns you of that scenario and gives you the option of editing links via a pop-up dialog.