Linking a Microsoft Project to Excel worksheet data adds dynamic functionality to your Project files. Usually, the linking process – also called "embedding" – is a short and relatively simple process in which you copy and paste data from Excel into Project and then add a link back to the Excel worksheet. Changes you make to the Excel worksheet will then automatically update in Project. Understanding the types of problems that make linking difficult or impossible can prevent some problems from ever occurring and make it easier and quicker to solve those that do.
External linking problems can result from user or system errors. While user errors, which most often involve incorrect data entry, data validation and/or referencing, can occur at any time, system errors result from working with Project and Excel in a multiuser network environment. Error messages can help you determine the nature of the problem. Messages relating to issues with data entry and/or validation take the form of those you commonly see in Excel while system errors often include the word “Exception.”
Excel information that will not dynamically update does not necessarily mean the link is bad, but rather that the link is in a locked status. Open the Excel worksheet, right-click on the cell containing the link, select “Paste Special” and make sure the “Paste Link” radio button to the left of the “As” window is the active button. Data entry errors, however, can prevent Project from establishing a proper link. One of the most common is a “#NAME?” error. Incorrect spelling, forgetting to enclose text entered in a formula in double quotation marks and omitting the colon in a range reference can all cause this type of error. Changing the arrangement of an Excel worksheet by adding, deleting or moving columns can also break a link, so make sure to update links whenever you edit a worksheet.
Working with Excel and Project in a network environment requires access to the Team Foundation Server. Network issues that prevent you from connecting to the server will also cause problems with links. Other link issues can arise from two or more users attempting to modify Excel worksheet data simultaneously, work items appearing in the incorrect order in a tree list or adding "orphaned" work items – those not connected to a parent task.
The more links Project contains, the more important it becomes to periodically review their statuses and make sure all are functioning correctly. Although Excel cannot locate and perform auto-checking as it can with internal links, Excel 2007 and later versions allow you to group and locate external links using “Find & Select.” Although you will still need to check each link manually by clicking on it to ensure it works, placing external links in groups according to where they occur, such as in a cell, chart title or data series makes them easier to find, especially if Project includes numerous links.
- Microsoft Office: Link or Embed Excel Data into Project
- Microsoft Office: Correct a #NAME? Error
- Microsoft Support: XL2000: External Link Is Not Updated When Source Cell Is Moved
- Microsoft Developer Network: Troubleshooting Microsoft Excel and Microsoft Project Work Item Integration
- Microsoft Developer Network: Resolve Invalid Links in a Microsoft Excel Tree
- Microsoft Office: Find External References (Links) in a Workbook
- Photo Credit Ryan McVay/Digital Vision/Getty Images