Excel can adjust references for you   When you insert or delete rows or columns in a source workbook, make sure you first open any workbooks that contain links to the source workbook. If the linking workbooks are open while you modify the source workbook, Excel adjusts references in the linking formulas.

Use names in source workbooks   If you expect to make changes to a source workbook frequently, you may want to define names in the source workbook and use these names in the linking formulas. For example, instead of formula =[source.xls]Prices!B4, you could define name ThinBreadPrice for cell B4 in workbook Source.xls, and use linking formula =[source.xls]Prices!ThinBreadPrice. If you insert some rows above row 4 in Source.xls, links that use the name will continue to get the right price, even if the linking workbooks are closed while you modify Source.xls.

Use external names in linking workbooks   You can also define a name in a linking workbook that refers to a cell or range in a source workbook. For the procedure, type name external cells in the Office Assistant or on the Answer Wizard tab in the Excel Help window, click Search, and then click "Create a name that refers to cells in another workbook."

Updating and maintaining links

How Excel updates links depends on the type of link, and whether the source workbook is open.

If the source workbook is already open, Excel automatically updates links that use a drive letter in the path (the default when you create links).

For links that use UNC names, when you open a linking workbook, Excel gives you a choice of updating the linked information from the source workbook or keeping the version of the information that was last saved in the linking workbook. Excel also gives you this choice when you open a linking workbook and the source workbook is closed.

Updating automatically

You can update all links in your workbooks automatically, regardless of the type of link and whether the source workbook is open or closed. Click Options on the Tools menu, click the Edit tab, and clear the Ask to update automatic links check box. The next time you open a linking workbook, Excel updates the links without prompting you. This Excel setting affects all workbooks that you open.

Updating manually

When the source workbook is unavailable or you don't want to work with updated information, you can skip updating the links. Before opening a linking workbook, click Options on the Tools menu, click the Edit tab, and make sure the Ask to update automatic links check box is selected (this is the default). Open the linking workbook, and click No in response to the prompt to update the links.

When you're ready to update the links, click Links on the Edit menu. In the Links dialog box, click the source workbook for each link that you want to update and click Update Now.

When a source workbook is unavailable

Whether you're updating links manually or automatically, if Excel can't find a source workbook, Excel displays the File Not Found dialog box with the name of the missing source workbook in the File name box. You can use this dialog box to locate and select the source workbook. To search other locations on your network, click the Tools menu and then click Map network drive. You can also click Cancel in this dialog box to skip updating the links.

When the source workbook has been renamed or moved

If the name or path to the source workbook has changed, and you use the File Not Found dialog box to find the workbook, Excel updates the links from the workbook you select, but doesn't change the workbook name in the linking formulas. To change the source workbook file name or path in the formulas, click Links on the Edit menu, click Change Source, and use the Change Links dialog box to locate and select the renamed source workbook.

Opening the source workbook for a link

To open a source workbook, click Links on the Edit menu, click the link for which you want to open the source workbook, and then click Open Source. Once you open the source workbook in this manner, the path to the workbook is no longer displayed when you click a linking cell, even if the link uses the UNC name.

Breaking and deleting links

To break a link, select the linking cell or cells, click the Copy button, click Paste Special, and then click Values. This replaces the linking formula with its value and deletes the link.

For macros to find and delete all the links in a workbook, connect to the Microsoft Knowledge Base, and then search under Excel for "delete links."

More information

For more information about links, type links between workbooks in the Office Assistant or on the Answer Wizard tab in the Excel Help window, click Search, and then click "About using formulas to calculate values on other worksheets and workbooks."

The Knowledge Base has information about managing links, including macros to open all workbooks linked to a given workbook and to delete links associated with formulas and hidden names. Connect to the Knowledge Base, and then search under Excel for "links."