Results 1 to 4 of 4
  1. #1
    Lounger
    Join Date
    Aug 2012
    Location
    Israel
    Posts
    25
    Thanks
    6
    Thanked 0 Times in 0 Posts

    vlookup link changes - suddenly

    This seems to be a know issue on the internet. It certainly happens to me quite often - probably because I have 2-10 excel files open at the same time - each one with many vlookup links.

    I have excel 2010.

    I have excel files and data sources on both drives at my disposal. I have many vlookup links such as:
    VLOOKUP(A11,'C:\TV\[customers.xls]Sheet1'!$C$5:$H$3000,2,FALSE)

    The problem is that the C:\ sometimes changes to F:\ without warning. I now spend several minutes a day testing my files if that happened. Then I fix it with a simple search/replace procedure.

    That is under control, I guess.

    However sometimes the changes to the formulas are quite profound.

    I am sure the changes occur because of various save/save_as actions that I do during the day, although I can't pin it down exactly.

    Does anybody know how to tell excel NOT to fiddle with formulas? Or perhaps excel could be taught to warn the user that changes in the formulas are about to happen ALLOW/DISALLOW.

    As I mentioned, this issue is causing many users great distress as a simple internet-search will reveal.

    Thank you for your help.
    Michael

  2. #2
    2 Star Lounger
    Join Date
    Dec 2009
    Location
    Manchester, United Kingdom
    Posts
    116
    Thanks
    8
    Thanked 17 Times in 16 Posts
    "as a simple internet search will reveal" - actually quite a complicated internet search but I did find it. And I assume you've read the causes and tried the solutions. The page Microsoft support direct you to at least tries to explain the process, but doesn't really offer a solution.

    http://support.microsoft.com/default...b;EN-US;328440

    Microsoft are not about to fix this anytime soon. One possible solution would be to use three workbooks. "source.xlsx" has the source data, and is in a remote location (i.e. needs a drive letter or unc path), "farlinks.xlsx" just has links to source.xlsx and is in the same folder as your main file. Links in your main file go to farlinks and only specify that file name, no path. Ideally your main file and farlinks are on your local drive. Because your main file and farlinks are always available on the same path Excel should not break the links in the main file when it is saved. Because farlinks never needs to be saved those links should not break either.

    Ian.

  3. The Following User Says Thank You to iansavell For This Useful Post:

    Michael Unger (2014-03-25)

  4. #3
    Lounger
    Join Date
    Aug 2012
    Location
    Israel
    Posts
    25
    Thanks
    6
    Thanked 0 Times in 0 Posts
    I would like to thank you for your answer. I also contemplated putting source files in both drives in similar locations. At this point I think it is more complicated than the problem itself.

    In short the advice of microsoft is: To work around this issue, do not open multiple workbooks that reference the same source during the same Excel session.

    I suppose with practice I can abide by that rule.

    Thanks again.
    Michael

  5. #4
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,631
    Thanks
    115
    Thanked 645 Times in 589 Posts
    Michael,

    What happens if you were to protect the sheet by locking the cells with the formulas?

    Although not a cure, I am wondering that if you place the following code into the Worksheet_Change event, will you be notified when the formulas for specific cells have changed. You will need to add the address of each of the problematic formulas to the case statement.

    formulas1.png

    The pop-up message appears if I manually change the formula or if I change it by a routine. Although I cannot duplicate your scenario, I am betting it will work for your issue as well.

    HTH,
    Maud

    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Cells.Count > 1 Then Exit Sub
    Select Case Target.Address
        Case "$B$6", "$G$5", "$H$5" 'ADD CELL ASSRESSES THAT HAVE PROBLEMATIC FORMULAS HERE
            MsgBox "The formula in Cell " & Target.Address & " has changed to " & _
            Target.Formula
    End Select
    End Sub
    Last edited by Maudibe; 2014-04-13 at 22:17.

Tags for this Thread

Posting Permissions

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