The information in this article applies to:
- Microsoft Excel 98 Macintosh Edition
- Microsoft Excel for Windows, versions 2.x, 3.0, 4.0, 4.0a, 5.x
- Microsoft Excel for Windows 95, version 7.0
- Microsoft Excel for OS/2, versions 2.20, 2.21, 3.0
- Microsoft Excel for the Macintosh, versions 1.x, 2.x, 3.0, 4.0, 5.0
SUMMARY
When you link more than one cell by using the Copy and Paste Link
commands, Microsoft Excel creates the resulting link as an array
formula. With an array formula, you cannot change individual cells
within the linked range.
To change links for only a portion of the range, link cells
individually instead of linking them with an array formula.
MORE INFORMATION
To link a range of cells without creating an array formula for the
link, link each cell individually by linking the first cell in the
range, changing the link reference to a relative reference (if
necessary), and then filling in the rest of the destination range with
the link formula.
This procedure works for links between cells on the same worksheet or
between cells on two or more worksheets.
For example, if you use the Copy and Paste Link commands to link the
range destination C1:D10 to the source range A1:B10, each cell
in the destination range contains the formula {=$A$1:$B$10}. Note that this
is an array formula that refers to the entire range A1:B10.
Creating Individual Links to Cells in a Range
To create these links, follow these steps:
- Create a link from the first cell (upper left) in your destination
range to the first cell in your source range (upper left).
- Copy that link formula to the remaining cells in your destination
range.
Creating a Link to the First Cell in the Source Range
To create this link, follow these steps:
- In the first cell of your destination range (upper left) type an
equal sign (=) (Don't press the ENTER or RETURN key).
- Scroll through the worksheet to the source range (change to the
source worksheet if you are linking cells on different worksheets)
and select the first cell in your source range (upper left).
- Press the ENTER key (the RETURN key on the Macintosh).
If the cells are on the same worksheet, Microsoft Excel enters the
link formula using a relative reference (for example; =A1). If the
cells are on different worksheets, Microsoft Excel uses a fixed
reference that includes the source sheet name (for example;
=Sheet1!$A$1).
- If you are linking cells on different worksheets, change the
reference in your link formula in the destination cell to a
relative reference by removing the dollar signs. For example,
change =Sheet1!$A$1 to =Sheet1!A1.
NTOE: To do this quickly, select the destination cell, select the
link formula in the Formula Bar, and press the F4 key three
times. (Press COMMAND+T on the Macintosh.)
After you link the first cell and, if necessary, change the reference
to a relative reference, copy the link formula to the remaining cells
of your destination range.
Copying the Link Formula to the Rest of Your Destination Range
To copy the link formula, follow these steps:
- Select the first cell in your destination range (the one you linked
by using steps 1 through 4 above).
- On the Edit menu, click Copy.
- Select your entire destination range, including the first cell you
linked.
- On the Edit menu, click Paste.
Because you are using a relative reference, Microsoft Excel increments
the column letter (or number) and row number for each formula it adds
(for example, A1, B2, C3...and so on) thereby setting up individual
links to each corresponding cell in your source range.
REFERENCES
"Microsoft Excel User's Guide 1," version 4.0, pages 128-129
|