XL: How to Link Cell Ranges Without Using an Array Formula

ID: Q93185

The information in this article applies to:

Microsoft Excel for Windows, versions 2.0, 3.0, 4.0, 5.0
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.0, 2.20, 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

Additional query words: 1.0 1.00 1.03 1.04 1.06 1.5 1.50 2.0 2.00 2.01 2.1 2.10 2.2 2.20 2.21 3.0 howto page 5.0c 5.0a numbers

Keywords : xlformula
Version : MACINTOSH:1.0,2.20,3.0,4.0,5.0; WINDOWS:2.0,3.0,4.0,5.0,7.0
Platform : MACINTOSH WINDOWS
Issue type : kbhowto kbinfo