Results 1 to 13 of 13
  1. #1
    Star Lounger
    Join Date
    Nov 2004
    Location
    San Diego, California, USA
    Posts
    95
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Can't Update Links (Excel 2000)

    I have copied and pasted using "Paste Special | Link | Text" approximately 30+ date fields and 30+ "% Complete" fields from a "master" project plan (Project 2000). I did not enter all of the links at the same time (i.e., over several days/logons). It appears that all of the links work and automatically update when I open the spreadsheet and select "Yes" (to update the links) EXCEPT for the last group that I linked into the spreadsheet. I cannot manually update these links via the Edit | Links method either. I have checked the "Update Remote References" box on the Calculate tab in the Options, also. At one point I tried to delete some of the links and relink them, and appeared successful, but Excel then skipped those numbers (i.e., link #s 45-47). The files involved are stored on network drives.

    Not sure if this is relevant, but I also have Project 2003 installed and force Project 2k file open prior to opening the spreadsheet. I have tried letting Project 2003 open instead, but did not have any better luck.

    I cannot post the files involved here due to Corp. IP & confidentiality.

    Any help would be appreciated!

    Don L.
    Don Liebman
    San Diego, CA 92115

  2. #2
    3 Star Lounger
    Join Date
    Feb 2003
    Posts
    363
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Can't Update Links (Excel 2000)

    I'm not familiar with Project 2.... but I gather from your enquiry that you are able to have it and excel open at the same time. If so, what happens if you select a destination cell in xl and enter "=" (no quotes) then select the source in Project? as one could do in two xl workbooks?

    Don't know if this helps?

  3. #3
    Star Lounger
    Join Date
    Nov 2004
    Location
    San Diego, California, USA
    Posts
    95
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Can't Update Links (Excel 2000)

    Paul,

    I can have both Project 2000 and Excel 2000 open at the same time, but when I type ''='' (w/o quotes) into an Excel cell and then change the focus to the cell in Project 2000 there is no transfer of data or link information from Project 2000 back into the spreadsheet cell (i.e., only the "=" is still in the cell).

    Any other ideas to try? I am having to update almost half of my cell links (~30) manually every week until I can find a solution to this problem.

    Thank you,

    Don
    Don Liebman
    San Diego, CA 92115

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Can't Update Links (Excel 2000)

    I don't have nor have ever used Project2000.

    What is different about the old links and newer links? Do they refer to different files? Are all the non-working ones from a particular file? Were they created differently?

    If you recreate the old ones do they continue to work or when you recreate them do they also become non updating? Or do only particular ones become non-updating?

    What do the "non-updating" links display in the cells: ie what does the formula return?
    Steve

  5. #5
    Star Lounger
    Join Date
    Nov 2004
    Location
    San Diego, California, USA
    Posts
    95
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Can't Update Links (Excel 2000)

    Steve,

    All of the links are to the same "Master" project plan file (with an .mpp file extension). The only reason that there are "old" and "new" links is due to the fact that the original spreadsheet contained a column for "% Complete", and I linked in all of the data for that column - but over 2 days. Then, I went back and decided to add a new column for the Baseline (i.e., originally planned) Finish Date to the spreadsheet. I am not sure that I opened the files in a consistent manner, and have tried to open the Project file as read-only and read-write, without any better results. FYI also, the 2 files involved reside in different network shared drives. Should I move them into the same network shared folder?

    I have gone back to one of the "old" links and tried to re-link, only to find a new issue. The link numbers for the old links ended with 45 (i.e., " =MSProject.Project.9|'shared01WorkGroupxxxyyyzzzMa ster.mpp'!'!LINK_45' "), so I deleted all of the links beginning with 45 through the last, or most recently linked item. Excel got stuck on link number 45, and every time I attempt to paste a new link the same linked data appears in the new cells with the same link number - 45!!!

    Is there a "best practices" manner for creating links between Office 2000 files that I should be following?

    Thank you,

    Don
    Don Liebman
    San Diego, CA 92115

  6. #6
    3 Star Lounger
    Join Date
    Feb 2003
    Posts
    363
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Can't Update Links (Excel 2000)

    <font color=blue>when I type ''='' (w/o quotes) into an Excel cell and then change the focus to the cell in Project 2000 there is no transfer of data </font color=blue>

    I don't know if this is just semantics, but when linking to another workbook, after you change the focus to a new wbk the link in not established and there is no transfer of data until you click on the ENTER BUTTON.

  7. #7
    Star Lounger
    Join Date
    Nov 2004
    Location
    San Diego, California, USA
    Posts
    95
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Can't Update Links (Excel 2000)

    Paul,

    Nothing happens when I hit ENTER in the Project file after having entered "=" in Excel and changing focus, nor in the Excel file after hitting ENTER either. I feel like there is something basic regarding this linking procedure that I am missing the boat on.

    Don
    Don Liebman
    San Diego, CA 92115

  8. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Can't Update Links (Excel 2000)

    I will "go out on a limb" and make a speculation and ask questions (remember, I do not use or know Project, so my questions may seem ignorant, but I admit ignorance in Project).

    Are you "breaking the links" by editing too much in Project? I know (based on how excel links to other excel workbooks) that the links "locations" are hardcoded in the formulas. If you link in the excel spreadsheet to a "location/cell" in a project "table" (sorry, don't know the nomenclature of the project "objects") and later do something in project that "moves" the location, excel might still be looking at the old location.

    An example in Excel to explain my "hypothesis".
    If you have a workbookA that you want to "link" to workbookB, Sheet1, cell Q15. If you create the formula in WkbA to link to that cell in wkbB it will update and work fine if you edit the contents of the cell in workbook B. It does not matter if the workbookA is open at the time, when it is opened, it will get the correct value.

    If workbookA is closed, and you open workbookB and MOVE cell Q15 of sheet1 (move by moving the cell, inserting/deleteing rows before row 15 or inserting/deleting columns before column q) when you later open Workbook A and update links, it will still update Q15 even if the cell you actually want was moved: workbook A does not know that workbook B had the cell moved.

    Are you "moving" the location contents in project and "corrupting" the link location?

    Steve

  9. #9
    Star Lounger
    Join Date
    Nov 2004
    Location
    San Diego, California, USA
    Posts
    95
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Can't Update Links (Excel 2000)

    Steve,

    I do not believe that I have moved data to cause this problem, but I do think that there has been some sort of data corruption. My reasoning is due to the fact that an entire column of data in my Excel sheet is linked fine with one column of data in Project 2k, but the newest linked column is having the fits (links for about 20% are working). I have copied the Excel file to a new file, and am appearing to be linking fine now (I haven't finished linking all fields, but did 6 or 7, closed the file & reopened / updated links, and it redisplayed the correct data). I tried this after looking through the MS Knowledgebase this afternoon and found an article stating that different workbooks have their own "link tables", and it is possible that I may have opened another spreadsheet with links while working on this one, or changed some Tool | Option on the Calculate tab midway through that caused some problem resulting in this mess.

    Thank you,

    Don
    Don Liebman
    San Diego, CA 92115

  10. #10
    3 Star Lounger
    Join Date
    Feb 2003
    Posts
    363
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Can't Update Links (Excel 2000)

    Don,
    This sound like perhaps your original Excel file may be corrupted.
    If you start with a fresh workbook as a test, make a few links to Project, close xl, edit Project "cells" which are linked to, then reopen your test workbook, do your xl links update properly?
    If so, I would suspect your original xl file is corrupted.

  11. #11
    Star Lounger
    Join Date
    Nov 2004
    Location
    San Diego, California, USA
    Posts
    95
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Can't Update Links (Excel 2000)

    Paul,

    I ran the test you suggested, and the spreadsheet updated OK as long as I kept the Project 2k file open.

    Here's what I did:

    1) I opened the Master project plan.
    2) I opened a new instance of Excel (after closing any previousl;y open ones).
    3) I added 3 links to the xl sheet (interestingly enough the link #s were 45, 46, & 47 - these were the link #s where my issues began in the other xl file)
    4) I saved, then closed xl completely.
    5) I added a new task to one of the sections affected by my link (my links are all to summary lines), with a an end date far enough in the future that I would be assured it would change the Baseline Finish Date, and baselined the task and summary line.
    6) I reopened the new xl file, responded 'Yes' to the prompt to update the links, and the links updated OK.
    7) I save the xl file and closed xl down completely.
    8) I did not save Project and closed it down completely (so I would lose the new changes).
    9) I reopened only xl, responded 'Yes' to the prompt to update the links, and all 3 linked fields showed "#NAME?" - this is essentially what is happening in my "production xl file, also.

    The new links that I thought were linking OK yesterday, are experiencing these same symptoms today, i.e., the problem links are displaying "#NAME?".

    Do you think this is corruption of the link table in xl?

    Thanks,

    Don
    Don Liebman
    San Diego, CA 92115

  12. #12
    3 Star Lounger
    Join Date
    Feb 2003
    Posts
    363
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Can't Update Links (Excel 2000)

    OK

    Now do the above steps 1-9 again only this time in Step8, save your Project and close it. then reopen xl and see if it will update to the closed Project.

  13. #13
    Star Lounger
    Join Date
    Nov 2004
    Location
    San Diego, California, USA
    Posts
    95
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Can't Update Links (Excel 2000)

    Paul,

    It worked!

    Apparently, since I have not been "saving" Project all of the time after linking, it must not have saved some information with the .mpp file. But that solved it!

    Thank you!

    Don
    Don Liebman
    San Diego, CA 92115

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •