Create and Manage Links to Other Workbooks in Excel 2000 

A link is a formula that gets data from a cell in another workbook. When you open a workbook that contains links (a linking workbook), Microsoft Excel reads in the latest data from the source workbook or workbooks (updates the links).

Use links when you want to maintain information in a single workbook, but also use the information in other workbooks. For example, if your product prices change frequently, you might keep a master price list. Other workbooks that use the price data in calculations, such as purchase orders, inventory valuations, or sales estimates, can create links to the price list workbook so calculations always use current prices. When prices change, you only have to enter the new prices in one place.

When you create a link, Excel creates a formula that includes the name of the source workbook in brackets [ ], followed by the worksheet name, an exclamation point, and the cell reference.

How to Create Links Between Workbooks

1. Open both workbooks.
2. In the source workbook, select the cells you want to link to and click the Copy button.
   
3. Switch to the destination workbook and click the upper left cell of the range where you want the links.
4. On the Edit menu, click Paste Special, and then click Paste Link.

You can also create a link starting from the linking workbook. Type an equal sign in a cell, switch to the source workbook, click the cell you want to link to, and then press ENTER.

You can copy and autofill linking formulas as you do other Excel formulas.

Putting Together a Set of Linked Workbooks

Before you make extensive use of links, it's a good idea to do some planning. Here are some tips for creating links:

Make links easy to track  There's no automatic way to find all the cells in a workbook that contain links. You might want to use a particular format or border style for cells containing links, or otherwise document where the links are, so that as your set of linked workbooks grows and gains complexity you'll be able to maintain it efficiently.

Turn on automatic calculation  Source workbooks that you link to should have automatic calculation turned on (this is the default). To turn automatic calculation on, click Options on the Tools menu, click the Calculation tab, and click Automatic under Calculation.

Avoid circular links  Circular links, where two workbooks contain links to each other, can make both workbooks slow to open and update.

Consider where you'll store linked workbooks  If you're using links to share data on your network, consider where the source and linking workbooks will be stored. Source workbooks stored on a network share are available to other network users, who can store their linking workbooks either on the network or on their own hard disks. Source workbooks on your hard disk, however, are only available to you, so if you put linking workbooks on a network share, other users won't be able to update links to source workbooks on your system.

Network drive letters in links  When you click a cell containing a link and the source workbook is closed, you'll see the full path to the source workbook. The path uses the drive letter you have mapped to the share. The links continue to work correctly for other users who map different drive letters to the same share.

You can edit linking formulas to use UNC names, such as \\myserver\myshare, instead of drive letters. UNC names can make links easier to update when several people will use a linking workbook, because Excel can update links that use UNC names even if users don't have a drive letter mapped to the network share. For example, you can change ='G:\[source.xls]Prices'!B5 to ='\\myserver\myshare\[source.xls]Prices'!B5. However, using UNC names can also reduce calculation speed, especially if your workbooks use calculation-intensive features such as the Solver add-in program.

File names and paths in links  Windows has a 255-character limitation on paths, which also limits the length of the paths you can use when you create links. You'll need to consider path length in naming files and setting up folder names and structures. In addition, if a workbook file name is longer than 122 characters, you can create links to the workbook but you can't change the links once created.

Avoid characters from the Excel linking syntax in file names and worksheet names. You can't create links to workbooks and worksheets with names that include the characters [ ] and !.

Linking to cells containing large amounts of data  Links can only return the first 255 characters in a cell. If you want to link to large amounts of data, first distribute it among several cells on the source worksheet. If you're linking to a cell with contents that will grow as you maintain the source workbook, keep the 255-character limit in mind.

Links in shared workbooks  You can create links to or from shared workbooks, but if one user creates links in a shared workbook while other users have the workbook open, when other users save they may see a #REF! error instead of the linked data. You can avoid this problem by creating all the links before you share the workbook, or at a time when other users aren't working on it.

Linking to password protected workbooks  Assigning a protection password to a source worksheet or workbook does not prevent users of linking workbooks who don't know the password from updating the links. If you want users to have to enter a password to update links, save the source workbook file with a password to open (click Save As on the File menu, click General Options on the Tools menu).