Temporarily Changing a Remote Link

ID: Q75873

The information in this article applies to:

Microsoft Excel for Windows, versions 2.x, 3.0, 4.0, 4.0a, 5.0
Microsoft Excel for OS/2, versions 2.2, 3.0
Microsoft Excel for the Macintosh, versions 2.x, 3.0, 4.0, 5.0

SUMMARY

If a file with remote links is opened in Microsoft Excel and the file to which the remote links refer is not found, Microsoft Excel prompts you to specify a file it can use as a substitute. Using this feature allows you to temporarily change the file from which you get data for a particular sheet.

NOTE: This change is only temporary. The remote links remain the same; their references are not updated.

MORE INFORMATION

The Links command in the File menu shows the names of files to which the active spreadsheet is linked. Microsoft Excel needs to be able to find each file that is listed if you select the Update References to Unopened Documents option upon opening the linked sheet. If it cannot find a file, you will be prompted to enter an alternate path or a different file to use for the link.

This is useful if, for example, you want to test several groups of data in one spreadsheet.

Open three new worksheets.

In Sheet1 and Sheet2, type the following:


       Sheet1     Sheet2
       ------     ------

       A1: 1      A1: 3
       A2: 2      A2: 4

Save both files.

Highlight cells A1:A2 in Sheet1 and choose Copy from the Edit menu.

Activate Sheet3 and choose Paste Link from the Edit menu. Type the following in A4:

=SUM(A1:A2)

Save Sheet3 and close it.

With Sheet3 closed, save Sheet1 to a different directory and delete it from the current directory.

Close Sheet1 and Sheet2.

Reopen Sheet3. You receive a dialog box asking if you want to "Update References to Unopened Documents?". Select Yes.

Microsoft Excel now attempts to search for Sheet1. Because it is saved to a different directory, a dialog box asks you to select an alternate file or directory. Select Sheet2.

Microsoft Excel updates the links in Sheet3 to reflect the data in cells A1:A2 in Sheet2, and cell A4 on Sheet3 now calculates to 7. As a result, you can cycle through all your data files and test each group of data with a minimum of effort.

It is important to note that the references in the Links command of the File menu are not actually changed when you choose a different data file. The links to the alternate data file are lost when you close your dependent sheet, even if you save it. However, the results of the alternate data links are saved.

To change these references permanently, select the Change option in the dialog box that appears when you choose Links from the File menu. This prompts you for the name of the file to which you want to refer and makes the change permanent.

REFERENCES

"User's Guide 1," version 4.0, pages 362-373
"Microsoft Excel User's Guide," version 3.0, pages 307-316

Additional query words: 2.0 2.00 2.01 2.1 2.10 2.2 2.20 2.21 3.0


Keywords          : 
Version           : 
Platform          : 
Issue type        :

Last Reviewed: March 22, 1999