Microsoft Office Tutorials and References
In Depth Information
Making an Exact Copy of a Formula
If the linked workbook is closed, you must add the complete path to the workbook reference. For
=’C:\MSOffice\Excel\[Budget Analysis.xlsx]Sheet1’!A1+A1
Although you can enter link formulas directly, you can also create the reference by using the
normal pointing methods discussed earlier. To do so, make sure that the source file is open.
Normally, you can create a formula by pointing to results in relative cell references. But, when
you create a reference to another workbook by pointing, Excel always creates absolute cell
references. If you plan to copy the formula to other cells, you must edit the formula to make the
references relative.
Working with links can be tricky and may cause some unexpected problems. For
example, if you use the File
Save As command to make a backup copy of the source
workbook, you automatically change the link formulas to refer to the new file (not usually
what you want). You can also mess up your links by renaming the source workbook file.
Making an Exact Copy of a Formula
When you copy a formula, Excel adjusts the formula’s cell references when you paste it to a
different location. Usually, adjusting the cell references is exactly what you want. Sometimes,
however, you may want to make an exact copy of the formula. You can do this by converting the cell
references to absolute references, as discussed earlier — but this isn’t always desirable.
A better approach is to select the formula while in edit mode and then copy it to the Clipboard as
text. There are several ways to do this. Here I present a step-by-step example of how to make an
exact copy of the formula in A1 and copy it to A2:
1. Select cell A1 and press F2 to activate edit mode.
2. Press Ctrl+Home to move the cursor to the start of the formula, followed by
Ctrl+Shift+End to select all the formula text.
Or you can drag the mouse to select the entire formula.
Note that holding down the Ctrl key is necessary when the formula is more than one line
long, but optional for formulas that are a single line.
3. Choose Home
Copy (or press Ctrl+C).
This copies the selected text to the Clipboard.
4. Press Esc to end edit mode.
Search JabSto ::

Custom Search