Q114710 - PRJ: Data Linked from MS Excel Not Updated When Moved

PRJ: Data Linked from MS Excel Not Updated When Moved

Q114710

The information in this article applies to:

Microsoft Project 98 for Windows
Microsoft Project for Windows 95, version 4.1
Microsoft Project for Windows, versions 3.0, 3.0a, 4.0
Microsoft Project for the Macintosh, versions 3.0, 4.0
Microsoft Excel for Windows, versions 3.0, 4.0, 5.0
Microsoft Excel for the Macintosh, versions 3.0, 4.0, 5.0

SYMPTOMS

If you are working with a Microsoft Project document that contains a link to a Microsoft Excel worksheet, the reference that points to the data in the Microsoft Excel worksheet may not be correctly updated if you move it in the Microsoft Excel worksheet.

CAUSE

When you copy data from Microsoft Excel and you use the Paste Link command to link it to a Microsoft Project document, the reference that points to the Microsoft Excel data uses the R1C1 reference style to create an absolute reference. This reference will be incorrect if you move the linked data in the Microsoft Excel worksheet.

WORKAROUND

To work around this problem, follow the appropriate procedure below to change the link in Microsoft Project so that it refers to a Microsoft Excel named range. When you use a named range, Microsoft Project can keep track of the source data even if it is moved.

To change a link in Microsoft Project version 4.0

In Microsoft Project, choose Links from the Edit menu.

Select the link you want to change and choose the CHANGE SOURCE button.

In the Source box, select the cell range (for example, R1C1:R1C2) and replace it with the range name (for example, Test). Choose the OK button.

To change a link in Microsoft Project version 3.0 or 3.0a

In Microsoft Project, choose Links from the File menu.

From the Link Type list, choose DDE Links.

Select the link you want to change and choose the CHANGE button.

In the From box, select the cell range (such as, R1C1:R1C2) and replace it with the range name (such as, Test). Choose the OK button.

MORE INFORMATION

If, for example, in a Microsoft Excel worksheet, cell A1 contains the task name t1 and cell B1 contains the duration value of 5d, and you copy this range and link it to the Name and Duration fields for Task ID 1 in Microsoft Project, a link will be stored in the format specified in the table below.


   Version of          Version of
   Microsoft Project   Microsoft Excel    Format of link
   ----------------------------------------------------------------
   3.0                 4.x or earlier     Excel|Sheet1!R1C1:R1C2
   3.0                 5.0                Excel|[Book1]Sheet1!R1C1:R1C2
   4.0                 4.x or earlier     Sheet1!R1C1:R1C2
   4.0                 5.0                Book1!Sheet1!R1C1:R1C2

In Microsoft Excel, if you define a name for the range A1:B1 as Test, using the Microsoft Project 3.0 example above, you can change the link reference above to show as follows:

Excel|Sheet1!Test

In Microsoft Excel, if rows or columns are inserted that force the linked range to move, Microsoft Excel updates the named range to reflect the new location of the data. Because Microsoft Project references the named range rather than the exact cell reference, your data will always be updated correctly.

Additional query words: 3.00a

Keywords : kbinterop
Issue type : kbprb
Technology :