Linking a value in an Excel cell to a cell in Project

[Allen_Hodge]

Hello all!

We link numerical values from Excel to Project (we use percent complete worksheets in Excel that post to "% Work" rows in Project). This works okay and updates in real time (as soon as the data is updated in Excel, Project shows the change). Our Project files can become large (some over 1000 lines long), and with multiple links, it is difficult to track them once put in place. We use the "Edit Links" command, which will show us all the available links, but it points back to the original Excel sheet (and it does not show which cell it points to). Likewise, we cannot determine which cells in our Project files have external links associated with them. 

Is there an way to know which cell in the Excel data that a value is being being pulled from, and likewise, which cell(s) in Project have the Excel link tied to them?

Thanks!!

 

 

[#2 · Accepted solution]

@Allen_Hodge 

You are using Paste Links to link Excel data to Project and I must warn you that paste links are fragile and prone to corruption. If they seem to be working for you I'd say you are very lucky.

With paste links there is no clear method for seeing both "source" and "destination" in the Edit LInks window. You can click on a link in Project and hit "open source" and you will be shown which Excel cell is the source, but it's a "hunt and peck" process. It may be possible to automate a "seek and find" process with VBA but a much better way to accomplish what you are doing is with VBA itself (i.e. no paste links).

However, since you are only importing percent complete, have you tried an import map?

John

[#3]

Thanks for the quick reply John! I will look into Mapping this information. Have a nice day!

[#4]

Okay, let me know if you need further help or, if I answered your question, please consider marking my response as the answer.
John