1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Excel link not change if saved other computer

Discussion in 'Business Applications' started by Willum, May 6, 2010.

Thread Status:
Not open for further replies.
Advertisement
  1. Willum

    Willum Thread Starter

    Joined:
    Jun 12, 2008
    Messages:
    137
    Hi all,

    I have a (I think) simple question.
    I have an Excel sheet with links to another Excel sheet.
    Now I want to let users download the first sheet (with links), save them on their computer and let them send the document back.

    When I get the document back, the links to the other excel sheet changed.
    Is there a way to let the document just stay the original link to the sheet instead of automatic 'update' it when it's saved on another pc and the link can't be found.

    So I want to have a cell with the code =if(A1=F6='[sheet 2.xls]Sheet1'!$F6.... stayed this way instead of putting \LOCALS~1\Temp\ etc in front of it.

    I've tested this document for myself and I didn't update the links or whatever, so maybe there's some way to prevent this from happening?
     
  2. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,608
    First Name:
    Hans
    Hi (again) Willum,
    I think the problem is that the users that download and open the file get the question to veryfy the link or links in the sheet.
    You can also reply that the links may be broken.
    If this is the case I don't think your sheet is intelligent enough to reestablish these links onec you get it back.
    You could, I think, I never tried it, Maybe add a vba module for your use only that when you get a sheet back this module checks those cells that should contain the link and rebuild it, but like I said, I don't know if this is feasible since the values in the cells will be /NB or similar.
    You could also save al the links with the cell reference for later use in a hidden sheet.

    I'm just thinking while I type and what I would do in such a case.

    Hope I have given you some ideas. Good luck
     
  3. Willum

    Willum Thread Starter

    Joined:
    Jun 12, 2008
    Messages:
    137
    Hi Hans,

    well, it's for the World Championship. I have an original file in which I will place the real outcomes. In the files that I get send back, I have some formula's that if the filled in fields are the same as what I fill in, the user gets points. So I don't have to check them manually.

    I'll check further on the internet for solutions. :)
     
  4. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,608
    First Name:
    Hans
    Hi Willum,
    I understand, it's the World Championship sweepstake?

    I hope for an orange final
     
  5. Willum

    Willum Thread Starter

    Joined:
    Jun 12, 2008
    Messages:
    137
    lol, true. But we'll see.
    It's really annoying that the excel file updates the links automatically and doesn't let it stay.
    The original link is R:\WK speelschema\[WK invulschema origineel.xls].
    After I download it on this pc, it places a code like 'R:\DOCUME~1\Name~1\LOCALS~1\Temp\[WK invulschema origineel.xls]

    I don't know why. I don't update links or something.
     
  6. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,608
    First Name:
    Hans
    Well I think you receive these in Outlook, if you open an Excel sheet in Outlook it is placed in the temp folder unless you save it first, in that case you save it in a folder where you want it that should be the same folder where it originally came from then I think the links would be no problem.
    It will take some admisntrative work in the sense of checking who an what but it its onely one sheet where values can be entered, you could make a folder for each of the participanst and save their excel sheet there
    When you open you can query for each user. I hope I have explained the idea but maybe that is an option?
     
  7. Willum

    Willum Thread Starter

    Joined:
    Jun 12, 2008
    Messages:
    137
    hmm strange, this time it worked. Maybe the users need to save on download instead of opening it directly. I'll go and play around once more. See if this solves it or not (then the users should all save the file instead of opening it first).
     
  8. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,608
    First Name:
    Hans
    Yep, because the problem is when they just open it the reference is set to the temp location.
    It's takes an extra step but when prompted to open or to save, it is better to save and then open.
    What happens is that people tend to open anattachment, edit it and save it, not noticing that it's located in the temp folder.
    Then tehy continue with something else and decide to edite it again, they open tha attachemnt and the changes made have disappeard because the file is still in Outlook and is the original.
    You see where I'm getting to.
    So, first save, then open and edit, save again and always send the saved version.
     
  9. Willum

    Willum Thread Starter

    Joined:
    Jun 12, 2008
    Messages:
    137
    true, but I got this file online on a server. So people literally download it. They don't get it through the email.
    I tried myself, when I save it it saves in my documents/downloads. When I open it, make some changes and press Save As and go to my documents, the links stay with the temp folder. Even if I mail it back to me and when I place it in the correct folder.

    So people HAVE TO save it on download. Don't know if it happens that the computer don't ask if to save or open but just opens it? That would cause a problem.
     
  10. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,608
    First Name:
    Hans
    Yes, it's a setting, if you don't do anything IE will download it to temp and sometimes to <profile>\My Downloads but that is different with each system, you can't trust the settings to be equal.
     
  11. Willum

    Willum Thread Starter

    Joined:
    Jun 12, 2008
    Messages:
    137
    well, we see.. Else I just copy the formula's from the original sheet into the 40 returned sheets. The formula's are in all exactly the same and in hidden cells.
     
  12. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/921399

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice