Results 1 to 9 of 9
  1. #1
    Star Lounger
    Join Date
    Sep 2003
    Location
    Houston, Texas, USA
    Posts
    87
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Recalculation (Excel 2002)

    Link to MSKB article provided by HansV - see <!help=19>Help 19<!/help>

    Most of my users are still on Excel 2000. However, a few of them have moved to Excel 2002. The 2002 users have problems with files that sent from other locations. These files have external links and the linked files are never sent. In 2000 they simply choose not to update. In 2002 Excel updates anyway and gives error messages dues to the missing links. <!mskb=327006>Microsoft Knowledge Base Article 327006<!/mskb> shows a workaraound by temporarily turning off automatic recalculation and turning on recalculate before save. What we have found is that as soon as the file is saved, all the errors show up again. The only way that we can handle this is to leave the recalculation on manual and use shift f9 when changes have been made to the data. We never make changes to cells that use the linked data.
    The manual recalculation is a problem for us.

    Does anybody have any idea how to handle this? We cannot get the linked files, the originator of the files refuses to do paste special values. We are caught in the middle.

    Thanks for any suggestions!

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Recalculation (Excel 2002)

    Put your foot down and refuse to handle workbooks with links to unavailable external data.

  3. #3
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Recalculation (Excel 2002)

    The originator of the files lacks an understanding of how links work. Perhaps you can use the currently masted <!post=Excel Star Post,394839>Excel Star Post<!/post>?
    -John ... I float in liquid gardens
    UTC -7DS

  4. #4
    Star Lounger
    Join Date
    Sep 2003
    Location
    Houston, Texas, USA
    Posts
    87
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Recalculation (Excel 2002)

    Thanks Hans. Unfortunately I have been turned down on that one.

  5. #5
    Star Lounger
    Join Date
    Sep 2003
    Location
    Houston, Texas, USA
    Posts
    87
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Recalculation (Excel 2002)

    Thanks for the link. Problem is the links need to be there when the file is sent back. You are corect - they do not understnad how links work.
    I throw up my hands and give up.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Recalculation (Excel 2002)

    You shouldn't allow the originator to make it your problem. If they send you a workbook with an essential part of a worksheet missing, do you come to the Lounge, meekly asking if we know a workaround for the missing data? Of course not. But effectively, that is what the originator is doing by refusing either to replace the links by values, or supplying the source of the links.

  7. #7
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Recalculation (Excel 2002)

    You have a people problem, not an Excel problem.

    Can you give the originator an e-mail button that sends the file back to you after it calls that routine?

    Alternatively tell the originators boss that his/her files contain errors that he/she refuses to fix, and run the code to set all the errored links to error values and forward it to the boss.
    -John ... I float in liquid gardens
    UTC -7DS

  8. #8
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Ankeny, Iowa, USA
    Posts
    298
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Recalculation (Excel 2002)

    I would modify the macro John points you to so it puts a single quote in front of the linked formulas, make your changes to the spreadsheet, and send it back with instructions on how to run a macro that removes the quotes - that way the links stay intact. Of course, this won't work if you need the values from the links to do your part to the spreadsheet.

    Or, I would just send a 'data sheet' with whatever information it is you're having to update this user's spreadsheets with and put it on that person to enter the data themselves.

  9. #9
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Recalculation (Excel 2002)

    One final suggestion:<UL><LI>Your "EU" has file A - which he will not send you.<LI>You receive File B - which is linked to File A.<LI>Save File B to a network drive folder.<LI>Set up File C in exactly the same network folder - using an Excel 2000 machine - which links 1 for 1 directly to all the used cells in File B. This should take you very little time if you use relative links to set up the values, and then follow with Paste Special|Formats.<LI>Close File B. All the links to File A will remain "frozen".<LI>Use File C to enter all your data. As the links are not directly to File A, you should have no problem with Excel 2002.<LI>Send your "EU" back both Files B and C - explaining that they are l,inked to each other.<LI>If you want to give them a hard time, send them back File C with a written explanation of how it works. <img src=/S/evilgrin.gif border=0 alt=evilgrin width=15 height=15>[/list]HTH
    Gre

Posting Permissions

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